Window functions
Compute across related rows — rankings and running totals — without collapsing them.
- Use OVER to compute over a window of rows
- Rank rows with RANK / ROW_NUMBER
- Compute a running total with PARTITION BY
GROUP BY collapses rows into one per group — you lose the individual rows.
Window functions compute across a set of related rows while keeping every
row. They're the tool for rankings, running totals, and "compare each row to its
group."
OVER: a window per row
Add OVER (…) to a function and it operates over a window of rows rather than
collapsing them. Rank products by price, keeping every product:
Every product still appears, now with its rank. ROW_NUMBER() gives a unique
sequential number; RANK() ties share a rank.
PARTITION BY: windows per group
PARTITION BY splits rows into groups and applies the window within each — like a
GROUP BY that doesn't collapse. A running total of order quantities within each
order:
Within each order_id, running_qty accumulates down the rows — a running total
that resets for the next order. The same SUM with GROUP BY would give one row
per order; the window keeps them all.
Window functions are standard and supported by SQLite, PostgreSQL, and SQL Server. They replace a lot of awkward self-joins and subqueries — when you find yourself wanting "this row, plus something computed over its neighbours," reach for a window.
Where to go next
That completes Design & performance, and the SQL core's Advanced tier. The module lab puts windows and joins to work; dialect appendices cover where specific engines differ.