SQL window functions
Window functions calculate across related rows while keeping every row. They're behind rankings, running totals and “compared with last time” reports, and they're a favourite in data-analyst interviews.
OVER and PARTITION BY
Add OVER (…) to a function to make it a window function. PARTITION BY sets which rows are related; ORDER BY inside OVER sets their order. Even ordinary aggregates work this way.
SELECT name, species, age, MAX(age) OVER (PARTITION BY species) AS oldest_in_species FROM pets;
ROW_NUMBER vs RANK
ROW_NUMBER() always counts 1, 2, 3. RANK() gives ties the same number and then skips (1, 1, 3); DENSE_RANK() doesn't skip (1, 1, 2).
SELECT name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num, RANK() OVER (ORDER BY age DESC) AS rnk, DENSE_RANK() OVER (ORDER BY age DESC) AS dense FROM pets;
Top N per group
Number rows within each group, then keep the first few in an outer query. Window functions can't go directly in WHERE, which is why the subquery is needed.
SELECT species, name, age
FROM (
SELECT species, name, age,
ROW_NUMBER() OVER (PARTITION BY species ORDER BY age DESC) AS rn
FROM pets
) ranked
WHERE rn = 1;Running totals and LAG
SUM(x) OVER (ORDER BY …) adds everything so far. LAG(x) reads the previous row's value (NULL on the first row); LEAD(x) reads the next.
SELECT id, ordered_on, COUNT(*) OVER (ORDER BY id) AS orders_so_far, LAG(ordered_on) OVER (PARTITION BY customer_id ORDER BY ordered_on, id) AS previous_order FROM orders ORDER BY id;
Common mistakes
Trying to filter on a window function in WHERE. Wrap the query and filter outside it.
Using ROW_NUMBER when ties should share a place. Use RANK or DENSE_RANK.
Forgetting ORDER BY inside OVER for running totals, which gives the grand total on every row.
Questions
Does SQLite support window functions?+
Yes, since version 3.25 (2018), with the same syntax as PostgreSQL for everything on this page.
Window function or GROUP BY?+
GROUP BY returns one row per group. A window function keeps every row and adds the calculation alongside it.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.