π Every query in this post runs live, on real PostgreSQL or SQLite in your browser, at sqlstreak.com/sql/window-functions. Change them and see what breaks.
Window functions were the point where SQL "clicked" for me. Before them, every "rank these", "top 3 per category" or "compared with last time" question turned into a self-join I couldn't read a week later. With them, it's one extra clause.
The short version: a window function calculates across related rows but keeps every row. GROUP BY squashes a group into one row; a window function leaves the rows alone and adds the answer next to each one.
All examples use a tiny animal-shelter table:
pets(id, name, species, age, ...) -- 20 rows: dogs, cats, rabbits, birds
1. OVER and PARTITION BY
Put OVER (β¦) after a function and it becomes a window function. PARTITION BY says which rows belong together. Even plain aggregates like MAX work this way:
SELECT name, species, age,
MAX(age) OVER (PARTITION BY species) AS oldest_in_species
FROM pets;
| name | species | age | oldest_in_species |
|---|---|---|---|
| Ziggy | bird | 6 | 6 |
| Kiwi | bird | 2 | 6 |
| Mochi | cat | 2 | 11 |
| Luna | cat | 5 | 11 |
| Tofu | cat | 11 | 11 |
| β¦ | β¦ | β¦ | β¦ |
| Gizmo | dog | 10 | 10 |
| Clover | rabbit | 3 | 3 |
Still 20 rows. Each one now knows the oldest age in its own species. With GROUP BY species you'd get 4 rows and lose the names.
2. ROW_NUMBER vs RANK vs DENSE_RANK
The three ranking functions differ only in how they treat ties:
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;
| name | age | row_num | rnk | dense |
|---|---|---|---|---|
| Ziggy | 6 | 6 | 6 | 6 |
| Luna | 5 | 7 | 7 | 7 |
| Rocket | 5 | 8 | 7 | 7 |
| Noodle | 4 | 9 | 9 | 8 |
| Olive | 4 | 10 | 9 | 8 |
| Biscuit | 3 | 11 | 11 | 9 |
-
ROW_NUMBER()always counts 1, 2, 3β¦ Luna and Rocket are both 5, but one of them has to be 7 and the other 8. Which one is arbitrary: add a tie-breaker (ORDER BY age DESC, id) if it matters. -
RANK()gives ties the same number, then skips: 7, 7, 9. -
DENSE_RANK()gives ties the same number and doesn't skip: 7, 7, 8.
Interview tip: "find the second highest salary" is a DENSE_RANK question, because two people can share the highest.
3. Top N per group
This is the one I use most. Number the rows inside each group, then keep the first ones:
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;
| species | name | age |
|---|---|---|
| bird | Ziggy | 6 |
| cat | Tofu | 11 |
| dog | Gizmo | 10 |
| rabbit | Clover | 3 |
Why the subquery? SQL evaluates WHERE before window functions, so WHERE ROW_NUMBER() OVER (...) = 1 is an error. Compute the number first, filter outside. Change rn = 1 to rn <= 3 and you have "top 3 per species".
4. Running totals and LAG
Add ORDER BY inside OVER and aggregates become running: they include everything up to the current row. LAG(x) reads the value from the previous row (and LEAD(x) the next one). This uses a pizza-shop orders table:
SELECT id, customer_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;
| id | customer_id | ordered_on | orders_so_far | previous_order |
|---|---|---|---|---|
| 1 | 1 | 2026-03-01 | 1 | NULL |
| 2 | 2 | 2026-03-01 | 2 | NULL |
| 3 | 1 | 2026-03-03 | 3 | 2026-03-01 |
| 4 | 3 | 2026-03-04 | 4 | NULL |
| 7 | 2 | 2026-03-07 | 7 | 2026-03-01 |
previous_order is per customer, because of PARTITION BY customer_id. It's NULL on each customer's first order. That's the building block for "days since last order", churn, and retention queries.
Three mistakes I made so you don't have to
-
Filtering a window function in
WHERE. Wrap the query (subquery or CTE) and filter outside. -
ROW_NUMBERwhen ties should share a place. UseRANKorDENSE_RANK. -
Forgetting
ORDER BYinsideOVERfor a running total.SUM(x) OVER ()gives the grand total on every row, which looks plausible and is wrong.
Does SQLite support this?
Yes, since 3.25 (2018), with the same syntax as PostgreSQL for everything above. I checked every query in this post on both.
Try it
The interactive version of this guide lets you edit each query and switch between Postgres and SQLite. I built it as part of sqlstreak, a daily five-minute SQL practice app. The first lessons need no signup.
What's the window function you actually use at work? I'm collecting real-world examples for the next guide.
Top comments (0)