DEV Community

Cover image for SQL Window Functions in 5 Minutes: ROW_NUMBER, RANK, Running Totals, Top N
Muiduzzaman Lipu for Refactorly

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
month    revenue  running_total
2026-01  100      100
2026-02  150      250
2026-03  120      370
2026-04  200      570
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
Ann   Eng    120
Bob   Eng    100
Eve   Sales   90
Finn  Sales   75
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
month    revenue  change
2026-01  100      NULL
2026-02  150       50
2026-03  120      -30
2026-04  200       80
Enter fullscreen mode Exit fullscreen mode

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

These work in PostgreSQL, MySQL 8+, SQL Server and SQLite 3.25+.


I post one tested coding tip every day on YouTube at Refactorly. The 30-second version of this one is here.

Top comments (0)