Window functions — ranking, running totals, LAG/LEAD
Aggregate without collapsing rows. Ranks, running totals, period-over-period comparisons — the superpower of modern SQL.
Classical aggregation (GROUP BY) collapses rows into one per group. compute a value using a 'window' of related rows — but each input row still appears in the output.
Syntax: `function(args) OVER (PARTITION BY ... ORDER BY ... frame)`. **PARTITION BY** is 'GROUP BY for the window' — the window resets at each partition boundary. **ORDER BY** inside OVER defines the order within the partition (critical for running totals, LAG/LEAD).
Most useful window functions: ROW_NUMBER() (sequential 1..N within partition), RANK() / DENSE_RANK() (with ties), LAG(col, n) / LEAD(col, n) (previous / next row's value), SUM / AVG / COUNT OVER (running totals, moving averages).
Classic use cases: (a) Top-N per group: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) — first order per customer = row_number = 1. (b) Running total: SUM(amount) OVER (ORDER BY date) — cumulative sum. (c) Period-over-period: revenue - LAG(revenue) OVER (ORDER BY month) — month-over-month growth.
Window functions are a game-changer once you get them. Queries that took 3 self-joins become single-pass. Every major DB supports them now (Postgres, MySQL 8+, SQLite 3.25+, SQL Server, Oracle).
Grounded on https://www.postgresql.org/docs/current/tutorial-window.html
Next up
NULL handling — three-valued logic and gotchas
NULL means 'unknown' in SQL — not 'empty' and not 'zero'. Three-valued logic (TRUE, FALSE, UNKNOWN) quietly breaks naive queries.