Window Functions

By the end of this lesson you can rank rows inside each group, put a group figure beside every row, compare a row with the one before it, and filter on those results.

In Fundamentals, GROUP BY collapsed each group to one row; in Subqueries you recomputed a group average per row. A window function keeps every row and carries the group figure beside it. Examples use results(team, player, pts) and visits(site, day, hits).

OVER: an aggregate that does not collapse

OVER (PARTITION BY team) turns an aggregate into a window: the same AVG runs per team, but each player row survives.

SELECT team, player, pts,
       AVG(pts) OVER (PARTITION BY team) AS team_avg
FROM results WHERE team = 'blue' ORDER BY pts DESC;

blue|Oda|28|19.0
blue|Pip|17|19.0
blue|Quin|12|19.0

pts - AVG(pts) OVER (...) gives each player's gap to their team: 9.0, -2.0, -7.0. Without PARTITION BY the whole result is one window.

Ranking, and exactly what ties do

Inside OVER, ORDER BY sets the order the function walks. Red team, two players tied on 24:

SELECT player, pts,
       ROW_NUMBER() OVER (ORDER BY pts DESC, player) AS rn,
       RANK()       OVER (ORDER BY pts DESC)         AS rk,
       DENSE_RANK() OVER (ORDER BY pts DESC)         AS dr,
       NTILE(2)     OVER (ORDER BY pts DESC, player) AS half
FROM results WHERE team = 'red' ORDER BY pts DESC, player;

Kai|31|1|1|1|1
Lou|24|2|2|2|1
Max|24|3|2|2|2
Ned|9|4|4|3|2

ROW_NUMBER never repeats, so it splits the tie arbitrarily. RANK repeats, then skips: 1, 2, 2, 4. DENSE_RANK repeats and skips nothing: 1, 2, 2, 3. NTILE(2) deals the rows into 2 near-equal buckets.

ORDER BY inside OVER makes an aggregate cumulative

Add ORDER BY to an aggregate window and the group total becomes a running total:

SELECT day, hits,
       SUM(hits) OVER (PARTITION BY site)               AS site_total,
       SUM(hits) OVER (PARTITION BY site ORDER BY day)  AS running
FROM visits WHERE site = 'shop' ORDER BY day;

2025-03-01|120|560|120
2025-03-02|90|560|210
2025-03-03|200|560|410
2025-03-04|150|560|560

Order by a column unique within the partition, as day is here; ties on that key belong to the frame, covered in the Advanced tier.

LAG and LEAD

LAG(x, n, default) reads x from n rows back in the window order (n defaults to 1); LEAD reads forward. The first row has no predecessor, so without a default it gets NULL:

SELECT day, hits,
       LAG(hits) OVER (ORDER BY day)                 AS prev,
       hits - LAG(hits, 1, hits) OVER (ORDER BY day) AS delta
FROM visits WHERE site = 'shop' ORDER BY day;

2025-03-01|120||0
2025-03-02|90|120|-30
2025-03-03|200|90|110
2025-03-04|150|200|-50

Windows run late

Order of evaluation: FROM, WHERE, GROUP BY, HAVING, windows, SELECT output, ORDER BY. Two consequences.

A window cannot be in WHERE. WHERE ROW_NUMBER() OVER (ORDER BY pts) = 1 fails with misuse of window function ROW_NUMBER(). Compute it in a CTE, filter in the query that reads it:

WITH r AS (
    SELECT team, player,
           ROW_NUMBER() OVER (PARTITION BY team ORDER BY pts DESC, player) AS n
    FROM results
)
SELECT team, player FROM r WHERE n = 1;   -- blue|Oda and red|Kai

A window only sees rows that survived WHERE. Adding AND hits >= 120 to the LAG query above deletes the 90 first, so 2025-03-03 compares against 120 and shows a delta of 80 instead of 110. Compute the LAG in a CTE, filter after. Windows also run after GROUP BY, so RANK() OVER (ORDER BY SUM(pts) DESC) ranks whole teams by total.

Common mistakes

• ROW_NUMBER for a "keep ties" question. Rows tied at the cut-off get different numbers, so one is dropped and the output is a row short. Use RANK or DENSE_RANK.
• Forgetting PARTITION BY. The ranking runs over the whole table, so only the overall leaders survive.
• Filtering before the LAG. The neighbour is no longer the real previous row, so every change after a removed row is wrong. Order the LAG by the real sequence (a date), not by id.

Your exercise

Keep each customer's orders whose amount is among their two highest distinct amounts, with the change from their previous order by date. The starter prints every order with a change of 0. Tied amounts must both survive, and the previous order is judged before anything is filtered out.