SQL Window Functions - Basics

1. OVER()

Applies a function over all rows if not partitioned.

SELECT name, salary, 
       AVG(salary) OVER() AS avg_salary 
FROM employees;

Calculates the overall average salary for every row.

2. PARTITION BY

Divides result into groups (like GROUP BY) within the window function.

SELECT department, name, salary,
       AVG(salary) OVER(PARTITION BY department) AS dept_avg
FROM employees;

Computes average salary per department.

3. ROW_NUMBER()

Gives a unique row number per partition (or for the full set).

SELECT name, department,
       ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Ranks employees by salary within departments. Always unique.

4. RANK()

Assigns the same rank to ties, but skips the next rank.

SELECT name, salary,
       RANK() OVER(ORDER BY salary DESC) AS rank_salary
FROM employees;

If two people have the same salary, they get the same rank; next rank is skipped.

5. DENSE_RANK()

Similar to RANK() but does not skip ranks.

SELECT name, salary,
       DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_rank_salary
FROM employees;

Tied salaries share a rank, but the next one is not skipped.

6. LEAD()

Accesses the next row’s value in the result set.

SELECT name, salary,
       LEAD(salary, 1, 0) OVER(ORDER BY salary DESC) AS next_salary
FROM employees;

Shows the next higher salary. Defaults to 0 if there's no next row.

7. LAG()

Accesses the previous row’s value.

SELECT name, salary,
       LAG(salary, 1, 0) OVER(ORDER BY salary DESC) AS prev_salary
FROM employees;

Shows the previous salary. Defaults to 0 if there's no previous row.

Thanks to sql window functions