SQL Window Functions
You know how to GROUP BY. But the moment a question is "what's each row's rank within its group?" or "how does this row compare to the one before it?" or "show me a running total alongside every line," GROUP BY lets you down — it crushes your rows into summaries and throws away the detail you wanted to keep. Window functions are the fix. They run the same kind of math across a set of related rows but leave every row standing, with its answer attached.
This is the feature that separates people who query data from people who analyze it. Once it clicks, a whole class of "I'd have to do that in a spreadsheet" problems becomes one line of SQL.
How to read this
Read the three phases in order — they build on each other. Phase 1 gives you the mental model that makes everything else obvious: a window function adds a column without removing a row. Phase 2 is the working core you'll use daily. Phase 3 is the patterns that look like magic until you've seen them once. Run the SQL examples in any database that supports windows (PostgreSQL, SQLite 3.25+, MySQL 8+, SQL Server, BigQuery, DuckDB) — that's nearly all of them now.
The phases
- The window, not the group — what a window function actually is, and why it doesn't collapse rows
- OVER, PARTITION BY, ORDER BY — the everyday core: ranking, running totals, comparing to the previous row
- Frames, moving averages, and top-N-per-group — the deeper payoff and the patterns that earn their keep