Unit 5: Window functions: ranking, running totals and comparing rows
Calculate across related rows — rank them, compare each with the one before, keep running totals — without losing the individual rows the way GROUP BY does.
The big picture
Window functions are the single most tested "advanced" SQL topic. Top N per group, month-on-month growth, running totals, streaks — all of them become short once you think in windows: for each row, look at a set of related rows, and work something out.
Lessons in this unit
- What a window function is: OVER and PARTITION BY: A window function calculates a value for each row from a set of related rows, its "window", which is defined by OVER (PARTITION BY … ORDER BY …). Unlike GROUP BY, it keeps every row in the result.
- Ranking: ROW_NUMBER, RANK and DENSE_RANK: ROW_NUMBER numbers the rows 1, 2, 3, 4 with no ties. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3).
- LAG and LEAD: comparing with the previous or next row: `LAG(column)` returns the value from the previous row and `LEAD(column)` the value from the next row, in the order set by OVER (… ORDER BY …). That lets a row be compared with its neighbour.
- Running totals and moving averages: window frames: With ORDER BY inside OVER, SUM and AVG become running totals; a frame such as `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` limits the window to nearby rows, giving a moving average.
- Share of total, NTILE and FIRST_VALUE: `x / SUM(x) OVER ()` gives each row's share of the total. `NTILE(n)` splits the ordered rows into n roughly equal buckets. FIRST_VALUE and LAST_VALUE pull a value from the first or last row of the window.