Window Functions Drill (rank/lead/lag, frames)

# Window Functions Drill (rank/lead/lag, frames) Refresh target: read any window-function query and predict its output row by row. If you can simulate the frame in your head, gaps-islands and running totals fall out. ## 1. The mental model: partition, order, frame `ROW_NUMBER() / RANK() / DENSE_RANK()` differ only on ties. `LEAD(col, 1)` peeks at the next row in the ordered partition; `LAG(col, 1)` peeks back. Aggregates with `OVER (...)` need a frame: `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` gives the running total; the default frame (`RANGE ...`) misbehaves with duplicate order keys — prefer explicit `ROWS`. ## 2. Drill set (solve on the messy orders data) 1. Rank orders per customer by `order_total DESC`; keep top-2 per customer. 2. `LAG(order_date)` per customer → days-since-last-order; flag gaps > 30 days. 3. Running total of revenue per month with an explicit `ROWS` frame. 4. Gaps-and-islands: consecutive active days per user via the `date - ROW_NUMBER()` grouping trick. ## 3. Traps that fail skip-gates - Forgetting `PARTITION BY` collapses all users into one ranking. - `RANGE` default frame double-counts ties; always write the frame explicitly. - `LEAD/LAG` without `ORDER BY` inside `OVER` is nondeterministic. ## Recap Partition slices the data, order lines it up, frame bounds the aggregation window. Top-2-per-group, days-since-last via LAG, running totals via explicit ROWS frames, and the date-minus-row_number islands trick cover the skip-gate.