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.