SQL Window Functions: ROW_NUMBER, RANK and Running Totals

GROUP BY answers "what is the total per shelf" by throwing the individual rows away. That is exactly what you want for a summary, and exactly what you do not want for questions like:

• Number these rows 1, 2, 3 within each group.
• Show each payment next to the running total so far.
• Show each month next to the month before it.
• Keep the two dearest tools per shelf, as rows.

Every one of those needs a row to know something about its neighbours while staying a row. A window function does that. It looks at a set of related rows — its window — computes something over them, and attaches the answer to each row without removing any.

Window functions arrived in SQLite 3.25, and this playground runs 3.49, so everything below works here. PostgreSQL has had them since 9.0; MySQL only since 8.0. As always, each example builds its own table, because every run starts from an empty database.

OVER (): an aggregate that keeps the rows

The simplest window is "every row in the result". Write the aggregate you already know, then OVER ().

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350);

SELECT
  name,
  deposit,
  sum(deposit) OVER ()                                  AS all_deposits,
  round(deposit * 100.0 / sum(deposit) OVER (), 1)      AS percent_of_total,
  deposit - round(avg(deposit) OVER ())                 AS above_average
FROM tools
ORDER BY deposit DESC;

name           | deposit | all_deposits | percent_of_total | above_average
---------------+---------+--------------+------------------+--------------
tile cutter    |    1500 |         4500 |             33.3 |           600
cordless drill |    1200 |         4500 |             26.7 |           300
hedge trimmer  |     950 |         4500 |             21.1 |            50
wheelbarrow    |     500 |         4500 |             11.1 |          -400
hand saw       |     350 |         4500 |              7.8 |          -550
(5 rows)

Five rows in, five rows out, each carrying a total computed across all five. The same numbers with GROUP BY would have given you one row and no names. The subquery version of this — (SELECT sum(deposit) FROM tools) — works too; OVER () is shorter, and it is the doorway to everything else in this lesson.

PARTITION BY: one window per group

PARTITION BY splits the rows into groups and gives each row a window containing only its own group. It is GROUP BY for windows, except that nothing is collapsed.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350),
  ('post driver', 'garden', 1100);

SELECT
  shelf,
  name,
  deposit,
  count(*)     OVER (PARTITION BY shelf) AS tools_on_shelf,
  sum(deposit) OVER (PARTITION BY shelf) AS shelf_deposit,
  max(deposit) OVER (PARTITION BY shelf) AS dearest_on_shelf
FROM tools
ORDER BY shelf, deposit DESC;

shelf  | name           | deposit | tools_on_shelf | shelf_deposit | dearest_on_shelf
-------+----------------+---------+----------------+---------------+-----------------
garden | post driver    |    1100 |              3 |          2550 |             1100
garden | hedge trimmer  |     950 |              3 |          2550 |             1100
garden | wheelbarrow    |     500 |              3 |          2550 |             1100
hand   | hand saw       |     350 |              1 |           350 |              350
power  | tile cutter    |    1500 |              2 |          2700 |             1500
power  | cordless drill |    1200 |              2 |          2700 |             1500
(6 rows)

Each garden row knows there are three garden tools and what the dearest of them costs, while remaining a separate row with its own name. Getting that from GROUP BY would need a grouped query joined back to the table; here it is one clause.

ROW_NUMBER, RANK and DENSE_RANK

Add an ORDER BY inside the OVER (...) and a row also knows its position in the window. Three functions number rows, and they differ only in how they treat ties.

CREATE TABLE scores (id INTEGER PRIMARY KEY, member TEXT, points INTEGER);
INSERT INTO scores (member, points) VALUES
  ('Asha', 31),
  ('Bruno', 22),
  ('Cleo', 31),
  ('Devi', 18),
  ('Eero', 22);

SELECT
  member,
  points,
  row_number() OVER (ORDER BY points DESC, member) AS row_number,
  rank()       OVER (ORDER BY points DESC)         AS rank,
  dense_rank() OVER (ORDER BY points DESC)         AS dense_rank
FROM scores
ORDER BY points DESC, member;

member | points | row_number | rank | dense_rank
-------+--------+------------+------+-----------
Asha   |     31 |          1 |    1 |          1
Cleo   |     31 |          2 |    1 |          1
Bruno  |     22 |          3 |    3 |          2
Eero   |     22 |          4 |    3 |          2
Devi   |     18 |          5 |    5 |          3
(5 rows)

Read the three number columns across the tied rows:

• row_number() always gives 1, 2, 3, 4, 5 — every row a different number, ties broken by whatever you put in the window's ORDER BY. If you do not break the tie yourself, the database picks, and it may pick differently next time.
• rank() gives tied rows the same number and then skips: two rows at 31 are both rank 1, and the next is rank 3. That is how sports placings work.
• dense_rank() gives tied rows the same number and does not skip: 1, 1, 2, 2, 3.

Which one you want is a real decision. "The top 3 scores" with rank() <= 3 can return four rows; with row_number() <= 3 it returns exactly three, one of them chosen arbitrarily among equals.

Top N per group

This is the pattern people learn window functions for. Number the rows within each group, then keep the low numbers — and because a window function cannot appear in WHERE, the numbering happens in a CTE and the filtering outside it.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350),
  ('post driver', 'garden', 1100),
  ('mitre saw', 'power', 800);

WITH ranked AS (
  SELECT
    shelf,
    name,
    deposit,
    row_number() OVER (PARTITION BY shelf ORDER BY deposit DESC, name) AS place
  FROM tools
)
SELECT shelf, place, name, deposit
FROM ranked
WHERE place <= 2
ORDER BY shelf, place;

shelf  | place | name           | deposit
-------+-------+----------------+--------
garden |     1 | post driver    |    1100
garden |     2 | hedge trimmer  |     950
hand   |     1 | hand saw       |     350
power  |     1 | tile cutter    |    1500
power  |     2 | cordless drill |    1200
(5 rows)

Two rows per shelf where a shelf has two, one where it does not. The hand shelf is not padded out to two rows, because there is nothing to pad it with.

Why the CTE? WHERE runs before the SELECT list, so a window function does not yet exist when WHERE is evaluated — WHERE row_number() OVER (...) <= 2 is an error in every database. Compute it in one step, filter in the next. QUALIFY does this in one step in some warehouse dialects; standard SQL, SQLite, PostgreSQL and MySQL all use the CTE.

Running totals

Give the window an ORDER BY and an aggregate stops summing the whole partition: by default it sums everything from the start of the partition up to the current row. That is a running total.

CREATE TABLE takings (id INTEGER PRIMARY KEY, held_on TEXT, session TEXT, amount INTEGER);
INSERT INTO takings (held_on, session, amount) VALUES
  ('2026-03-07', 'repair cafe',  4200),
  ('2026-03-14', 'tool sharpen', 1800),
  ('2026-03-21', 'repair cafe',  3600),
  ('2026-03-28', 'seed swap',    900),
  ('2026-04-04', 'repair cafe',  5100),
  ('2026-04-11', 'tool sharpen', 2400);

SELECT
  held_on,
  session,
  amount,
  sum(amount) OVER (ORDER BY held_on)                        AS running_total,
  sum(amount) OVER (PARTITION BY session ORDER BY held_on)   AS running_by_session
FROM takings
ORDER BY held_on;

held_on    | session      | amount | running_total | running_by_session
-----------+--------------+--------+---------------+-------------------
2026-03-07 | repair cafe  |   4200 |          4200 |               4200
2026-03-14 | tool sharpen |   1800 |          6000 |               1800
2026-03-21 | repair cafe  |   3600 |          9600 |               7800
2026-03-28 | seed swap    |    900 |         10500 |                900
2026-04-04 | repair cafe  |   5100 |         15600 |              12900
2026-04-11 | tool sharpen |   2400 |         18000 |               4200
(6 rows)

The fourth column climbs to the grand total; the fifth restarts for each kind of session. Nothing else changed — the only difference between "total" and "running total" is the ORDER BY inside the OVER.

A moving average needs one more clause: the frame, which says how much of the ordered window each row can see.

CREATE TABLE takings (id INTEGER PRIMARY KEY, held_on TEXT, amount INTEGER);
INSERT INTO takings (held_on, amount) VALUES
  ('2026-03-07', 4200),
  ('2026-03-14', 1800),
  ('2026-03-21', 3600),
  ('2026-03-28', 900),
  ('2026-04-04', 5100),
  ('2026-04-11', 2400);

SELECT
  held_on,
  amount,
  sum(amount) OVER (ORDER BY held_on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  round(avg(amount) OVER (ORDER BY held_on ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS avg_last_three
FROM takings
ORDER BY held_on;

held_on    | amount | running_total | avg_last_three
-----------+--------+---------------+---------------
2026-03-07 |   4200 |          4200 |           4200
2026-03-14 |   1800 |          6000 |           3000
2026-03-21 |   3600 |          9600 |           3200
2026-03-28 |    900 |         10500 |           2100
2026-04-04 |   5100 |         15600 |           3200
2026-04-11 |   2400 |         18000 |           2800
(6 rows)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW spells out the running total the previous example got by default. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row window that slides down the table, so the first row averages one value, the second two, and the rest three.

LAG and LEAD

lag() reaches back to an earlier row in the window and lead() reaches forward, which is how you compare a row with the one before it without joining the table to itself.

CREATE TABLE takings (id INTEGER PRIMARY KEY, held_on TEXT, amount INTEGER);
INSERT INTO takings (held_on, amount) VALUES
  ('2026-03-07', 4200),
  ('2026-03-14', 1800),
  ('2026-03-21', 3600),
  ('2026-03-28', 900);

SELECT
  held_on,
  amount,
  lag(amount) OVER (ORDER BY held_on)                 AS previous,
  amount - lag(amount) OVER (ORDER BY held_on)        AS change,
  lag(amount, 1, 0) OVER (ORDER BY held_on)           AS previous_or_zero
FROM takings
ORDER BY held_on;

held_on    | amount | previous | change | previous_or_zero
-----------+--------+----------+--------+-----------------
2026-03-07 |   4200 | NULL     | NULL   |                0
2026-03-14 |   1800 |     4200 |  -2400 |             4200
2026-03-21 |   3600 |     1800 |   1800 |             1800
2026-03-28 |    900 |     3600 |  -2700 |             3600
(4 rows)

The first row has nothing before it, so lag() gives NULL and the subtraction gives NULL too. A third argument to lag() supplies a default instead — lag(amount, 1, 0) says "one row back, or 0 if there is no such row", which keeps the arithmetic working.

A worked example

One query with most of the lesson in it: every loan, numbered per member, with a running count and the gap since that member's previous loan.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL,
  taken_on  TEXT NOT NULL,
  deposit   INTEGER NOT NULL
);

INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO loans (member_id, taken_on, deposit) VALUES
  (1, '2026-03-04', 950),
  (1, '2026-03-21', 1200),
  (1, '2026-04-08', 500),
  (2, '2026-03-19', 500),
  (2, '2026-04-11', 350),
  (3, '2026-04-02', 1200);

SELECT
  m.name,
  l.taken_on,
  l.deposit,
  row_number() OVER (PARTITION BY m.id ORDER BY l.taken_on)   AS loan_no,
  sum(l.deposit) OVER (PARTITION BY m.id ORDER BY l.taken_on) AS running_deposit,
  COALESCE(
    CAST(julianday(l.taken_on) - julianday(lag(l.taken_on) OVER (PARTITION BY m.id ORDER BY l.taken_on)) AS INTEGER),
    0
  ) AS days_since_last,
  rank() OVER (ORDER BY l.deposit DESC) AS deposit_rank_overall
FROM loans AS l
JOIN members AS m ON m.id = l.member_id
ORDER BY m.name, l.taken_on;

name  | taken_on   | deposit | loan_no | running_deposit | days_since_last | deposit_rank_overall
------+------------+---------+---------+-----------------+-----------------+---------------------
Asha  | 2026-03-04 |     950 |       1 |             950 |               0 |                    3
Asha  | 2026-03-21 |    1200 |       2 |            2150 |              17 |                    1
Asha  | 2026-04-08 |     500 |       3 |            2650 |              18 |                    4
Bruno | 2026-03-19 |     500 |       1 |             500 |               0 |                    4
Bruno | 2026-04-11 |     350 |       2 |             850 |              23 |                    6
Cleo  | 2026-04-02 |    1200 |       1 |            1200 |               0 |                    1
(6 rows)

Three different windows are at work in one SELECT, which is allowed and common: two partitioned by member and ordered by date, and one ordered by deposit across everybody. julianday() turns a YYYY-MM-DD string into a day number so the subtraction means something — SQLite has no date type, so this is how date arithmetic is done here, where PostgreSQL would simply subtract two date values. And the overall rank() shows ties sharing a number: two loans at 1200 and two at 500.

Common mistakes

Putting a window function in WHERE

CREATE TABLE scores (id INTEGER PRIMARY KEY, member TEXT, points INTEGER);
INSERT INTO scores (member, points) VALUES ('Asha', 31), ('Bruno', 22), ('Cleo', 18);

SELECT member, points
FROM scores
WHERE row_number() OVER (ORDER BY points DESC) <= 2;

Error in the statement on line 4: misuse of window function row_number()

SQLite names the problem directly. WHERE is evaluated before the window functions are, so there is no row number yet to compare. Wrap the numbering in a CTE or a subquery and filter outside it, as the top-N example did. The same is true of GROUP BY and HAVING: window functions run after all of them.

Expecting the default frame to stop at the current row when values tie

CREATE TABLE takings (id INTEGER PRIMARY KEY, held_on TEXT, amount INTEGER);
INSERT INTO takings (held_on, amount) VALUES
  ('2026-03-07', 1000),
  ('2026-03-14', 2000),
  ('2026-03-14', 3000),
  ('2026-03-21', 4000);

SELECT
  held_on,
  amount,
  sum(amount) OVER (ORDER BY held_on) AS default_frame,
  sum(amount) OVER (ORDER BY held_on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM takings
ORDER BY held_on, amount;

held_on    | amount | default_frame | rows_frame
-----------+--------+---------------+-----------
2026-03-07 |   1000 |          1000 |       1000
2026-03-14 |   2000 |          6000 |       3000
2026-03-14 |   3000 |          6000 |       6000
2026-03-21 |   4000 |         10000 |      10000
(4 rows)

Two rows share a date, and the two running totals disagree on them. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and in RANGE mode "current row" means every row with the same ORDER BY value — so both 14 March rows see each other and both show 6000. Add ROWS and each row sees only the rows up to itself, giving 3000 and 6000. This is standard behaviour in PostgreSQL and MySQL too, and it is the most common reason a running total looks subtly wrong. When the ordering column can repeat, write ROWS explicitly — or order by something unique.

Confusing rank() with row_number() when taking a top N

CREATE TABLE scores (id INTEGER PRIMARY KEY, member TEXT, points INTEGER);
INSERT INTO scores (member, points) VALUES
  ('Asha', 31), ('Bruno', 22), ('Cleo', 22), ('Devi', 18);

WITH numbered AS (
  SELECT member, points,
         rank()       OVER (ORDER BY points DESC)         AS placing,
         row_number() OVER (ORDER BY points DESC, member) AS row_no
  FROM scores
)
SELECT member, points, placing, row_no
FROM numbered
WHERE placing <= 2
ORDER BY row_no;

member | points | placing | row_no
-------+--------+---------+-------
Asha   |     31 |       1 |      1
Bruno  |     22 |       2 |      2
Cleo   |     22 |       2 |      3
(3 rows)

placing <= 2 asked for the top two and returned three rows, because Bruno and Cleo tie for second and rank() gives them both a 2. Filtering on row_no <= 2 instead would have returned exactly two — Asha and Bruno — dropping Cleo for no reason other than her name sorting later.

The reverse surprise is just as common: with a tie at the top, ranks run 1, 1, 3, so placing <= 2 returns only the two joint leaders and silently drops the third-placed member who most people would call the runner up. Decide which question you are asking. rank() is for placings on a prize list; row_number() with a deliberate tie-breaker is for "give me exactly N rows".

Forgetting that a window function needs a modern database

CREATE TABLE scores (id INTEGER PRIMARY KEY, member TEXT, points INTEGER);
INSERT INTO scores (member, points) VALUES ('Asha', 31), ('Bruno', 22);
SELECT member, points, row_number() OVER (ORDER BY points DESC) AS place FROM scores;

member | points | place
-------+--------+------
Asha   |     31 |     1
Bruno  |     22 |     2
(2 rows)

This works, and the point is that it did not always. Window functions need SQLite 3.25 or later, MySQL 8.0 or later, and PostgreSQL 9.0 or later. If a query like this fails on a database you have been handed, with a syntax error pointing at the bracket after OVER, the version is the first thing to check — and the fallback is a correlated subquery or a self-join, which is why those are still worth knowing.

Next steps

That completes this track: you can create a table, filter and sort it, summarise it, join across tables, build a query out of named steps, change data safely, and now compute across rows without collapsing them. Next, work through the SQL exercise path — each one is a small broken or unfinished query to fix and run — and keep the SQL cheat sheet open beside the playground while you do.