Module 11: Window functions

Rank, number and compare rows without collapsing them: ROW_NUMBER, RANK, LAG and running totals.

1. OVER and PARTITION BY: windows vs GROUP BY: f(...) OVER (PARTITION BY x) adds a group value to every row without collapsing rows; it runs after WHERE, so filter on it from an outer query.
2. ROW_NUMBER, RANK, DENSE_RANK and top-N per group: ROWNUMBER never ties, RANK ties and skips, DENSERANK ties without gaps; top-N per group = rank with PARTITION BY in a subquery, filter outside.
3. LAG, LEAD and FIRST_VALUE: match-to-match change: LAG(x) / LEAD(x) read the previous / next row in the window order (NULL at the edges); always PARTITION BY the entity you are following.
4. Running totals and moving averages: frames: SUM(x) OVER (ORDER BY d) is a running total; the default frame is RANGE up to the current row and its peers, so use ROWS for one-row steps and a full frame for LASTVALUE.

Ends with a 13-question checkpoint (80% passes) and a project: The Orange Cap leaderboard.