Window Functions

GROUP BY collapses rows, Window (PARTITION BY) keeps rows.

Calculations across related rows without collapsing rows.

GROUP BY vs Window

GROUP BY collapses rows into one per group, while window functions with PARTITION BY keep all rows and add a calculated column.

-- GROUP BY reduces rows
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
-- 10 | 76500

-- Window keeps rows
SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;
-- John | 75000 | 76500

Golden rule: GROUP BY reduces rows. PARTITION BY keeps rows.

ROW_NUMBER

ROW_NUMBER assigns a unique sequential number to every row, even with ties, based on the specified ordering.

SELECT first_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

Unique number per row.

RANK

RANK assigns the same rank to ties and skips the next numbers, creating gaps in the ranking sequence.

SELECT first_name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

Example: 100000→1, 100000→1, 90000→3 (gaps).

DENSE_RANK

DENSE_RANK assigns the same rank to ties but does not skip numbers, producing a continuous ranking without gaps.

SELECT first_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

100000→1, 100000→1, 90000→2 (no gaps).

PARTITION BY

PARTITION BY divides rows into groups for window calculations, ranking employees separately within each department.

SELECT employee_id, department_id, salary,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Rank per department separately.

LAG

LAG retrieves a value from a previous row, useful for comparing a row with its predecessor in an ordered sequence.

SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;

Previous row value.

LEAD

LEAD retrieves a value from the next row, useful for looking ahead to the following row in an ordered sequence.

SELECT employee_id, salary, LEAD(salary) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

Next row value.

Running Total

A Running Total cumulatively sums values in order, partitioning by a key to calculate per-group progressive totals.

SELECT customer_id, transaction_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS running_total
FROM transactions;

Employees Above Department Average

This pattern finds employees whose salary exceeds their department average using a window average and an outer filter.

SELECT * FROM (
    SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
) x WHERE salary > dept_avg;