Window Functions

A window function computes across a set of rows related to the current row without collapsing them. That is the difference from GROUP BY: aggregation returns one row per group, a window function returns every row plus the computed value.

PARTITION BY divides the rows into groups; ORDER BY orders within each. AVG(salary) OVER (PARTITION BY department) puts each employee's departmental average on their own row, which GROUP BY cannot do without a join back.

The three ranking functions differ only in ties, and the distinction is asked constantly. Given scores 100, 90, 90, 80:

- ROW_NUMBER → 1, 2, 3, 4. Always distinct; tied rows get an arbitrary order. - RANK → 1, 2, 2, 4. Ties share a rank and the next value skips. - DENSE_RANK → 1, 2, 2, 3. Ties share a rank and nothing is skipped.

"Top 3 salaries per department" almost always means DENSE_RANK, because two people on the same salary should both count as third.

LAG and LEAD reach into the previous and next row — month-over-month change, time between a user's consecutive events, detecting gaps in a sequence. Before window functions this required a self join on n = n - 1, which is why older SQL looks the way it does.

Running totals come from a frame: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

The frame default is a genuine trap. With ORDER BY and no explicit frame, the default is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes all peer rows with the same ORDER BY value. With duplicate dates, every row on that date gets the same running total — the full day's sum. Specify ROWS when you want row-by-row.

Window functions run after WHERE, so you cannot filter on one directly. Wrap it in a CTE and filter outside.