SQL Window Functions in 5 Minutes: ROW_NUMBER, RANK, Running Totals, Top N

Running totals, rankings, "top 3 per category": without window functions these need messy self-joins and subqueries. With them, each one is a single line. Here's everything you need, with real results. I ran every query in SQLite and PostgreSQL.

Video version:

The sample data

-- employees -- name dept salary -- Ann Eng 120 -- Bob Eng 100 -- Cara Eng 100 -- Dan Eng 80 -- Eve Sales 90 -- Finn Sales 75 -- Gia Sales 60

The big idea: GROUP BY collapses rows, OVER keeps them

GROUP BY dept squashes each department into one row. A window function, written with OVER (...), calculates across a group of rows but keeps every row:

SELECT name, dept, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees;

name dept salary dept_avg Ann Eng 120 100 Bob Eng 100 100 Cara Eng 100 100 Dan Eng 80 100 Eve Sales 90 75 Finn Sales 75 75 Gia Sales 60 75

Every employee keeps their own row, next to their department's average. PARTITION BY is like GROUP BY, just without collapsing. That makes "salary vs department average" a one-liner.

ROW_NUMBER vs RANK vs DENSE_RANK

Bob and Cara both earn 100. Watch how each function handles the tie:

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, RANK() OVER (ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense FROM employees WHERE dept = 'Eng';

name salary row_num rnk dense Ann 120 1 1 1 Bob 100 2 2 2 Cara 100 3 2 2 Dan 80 4 4 3

ROW_NUMBER always counts 1, 2, 3, 4 and breaks ties arbitrarily. Add another column to ORDER BY if you need a stable order.
RANK gives ties the same number, then skips, so Dan is 4th.
DENSE_RANK gives ties the same number with no gap, so Dan is 3rd.

Running total in one line

Put ORDER BY inside OVER, and SUM adds up every row from the start to the current one:

SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_total FROM sales;

month revenue running_total 2026-01 100 100 2026-02 150 250 2026-03 120 370 2026-04 200 570

No self-join, no correlated subquery.

Top N per group (a classic interview question)

"Top 2 earners in each department":

WITH ranked AS ( SELECT name, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees ) SELECT name, dept, salary FROM ranked WHERE rn <= 2;

Ann Eng 120 Bob Eng 100 Eve Sales 90 Finn Sales 75

Why the CTE? Window functions run after WHERE, so you can't filter on them directly:

SELECT name FROM employees WHERE ROW_NUMBER() OVER (ORDER BY salary) <= 2; -- PostgreSQL: ERROR: window functions are not allowed in WHERE -- SQLite: misuse of window function ROW_NUMBER()

Want ties included (both Bob and Cara)? Swap ROW_NUMBER for RANK or DENSE_RANK.

Bonus: compare with the previous row (LAG)

SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM sales;

month revenue change 2026-01 100 NULL 2026-02 150 50 2026-03 120 -30 2026-04 200 80

LAG reads the value from the previous row, so month-over-month change is one subtraction. The first month has no previous row, so it's NULL. LEAD does the same thing, looking forward.

Cheat sheet

You want	Use
Calculate but keep every row	`OVER (...)`
GROUP BY without collapsing	`PARTITION BY`
Ranking	`ROW_NUMBER` / `RANK` / `DENSE_RANK`
Running total	`SUM(x) OVER (ORDER BY ...)`
Top N per group	`ROW_NUMBER` in a CTE + `WHERE rn <= N`
Previous / next row	`LAG` / `LEAD`