Exercise 9 of 10 · Window functions

The Running Total

What you will make

A loan ledger where each row shows both its own deposit and the total taken in up to and including that day — the shape every bank statement and sales dashboard uses.

The one new idea: ORDER BY inside OVER turns an aggregate into a running one

Running totals, moving averages and row-to-row comparisons are the everyday work of reporting, and before window functions existed each of them needed a self-join or a correlated subquery. One OVER clause replaces all of that, and the difference between a total and a running total is a single ORDER BY.

Go straight to the code ↓

An aggregate that keeps the rows

GROUP BY answers "what is the total" by throwing the individual rows away. That is right for a summary and useless when you want to see each row and something about its neighbours.

A window function does both. Write the aggregate you already know, add OVER (...), and it computes across a set of related rows — its window — while every row survives.

SQL

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, jars INTEGER);
INSERT INTO hives (label, jars) VALUES
  ('north hedge', 14), ('orchard', 31), ('lane end', 6), ('churchyard', 22);

SELECT
  label,
  jars,
  sum(jars) OVER ()                                 AS all_jars,
  round(jars * 100.0 / sum(jars) OVER (), 1)        AS percent_of_total
FROM hives
ORDER BY jars DESC;

Output

label       | jars | all_jars | percent_of_total
------------+------+----------+-----------------
orchard     |   31 |       73 |             42.5
churchyard  |   22 |       73 |             30.1
north hedge |   14 |       73 |             19.2
lane end    |    6 |       73 |              8.2
(4 rows)

Four rows in, four rows out, each carrying a total computed across all four. The empty brackets are the window: "every row in this result".

Give the window a direction

Put an ORDER BY inside the brackets and the aggregate stops summing the whole window. By default it sums everything from the start of the window up to the current row — which is a running total.

SQL

CREATE TABLE deliveries (id INTEGER PRIMARY KEY, delivered_on TEXT, jars INTEGER);
INSERT INTO deliveries (delivered_on, jars) VALUES
  ('2026-03-02', 14), ('2026-03-09', 31), ('2026-03-16', 6), ('2026-03-23', 22);

SELECT
  delivered_on,
  jars,
  sum(jars) OVER ()                    AS grand_total,
  sum(jars) OVER (ORDER BY delivered_on) AS running_total
FROM deliveries
ORDER BY delivered_on;

Output

delivered_on | jars | grand_total | running_total
-------------+------+-------------+--------------
2026-03-02   |   14 |          73 |            14
2026-03-09   |   31 |          73 |            45
2026-03-16   |    6 |          73 |            51
2026-03-23   |   22 |          73 |            73
(4 rows)

Same function, same column, two completely different meanings — and the only difference is three words inside the brackets.

Spell the frame out

The part of the window a row can see is called the frame, and the default is not quite what most people assume. It is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and in RANGE mode "current row" means every row with the same ORDER BY value:

SQL

CREATE TABLE deliveries (id INTEGER PRIMARY KEY, delivered_on TEXT, jars INTEGER);
INSERT INTO deliveries (delivered_on, jars) VALUES
  ('2026-03-02', 10),
  ('2026-03-09', 20),
  ('2026-03-09', 30),
  ('2026-03-16', 40);

SELECT
  delivered_on,
  jars,
  sum(jars) OVER (ORDER BY delivered_on) AS default_frame,
  sum(jars) OVER (
    ORDER BY delivered_on
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_frame
FROM deliveries
ORDER BY delivered_on, jars;

Output

delivered_on | jars | default_frame | rows_frame
-------------+------+---------------+-----------
2026-03-02   |   10 |            10 |         10
2026-03-09   |   20 |            60 |         30
2026-03-09   |   30 |            60 |         60
2026-03-16   |   40 |           100 |        100
(4 rows)

Two deliveries share a date, and the two running totals disagree on them: the RANGE version gives both rows the combined 60, while the ROWS version counts to 30 and then 60. Neither is a bug — they answer slightly different questions — but only one of them looks like a running total. When the ordering column can repeat, write ROWS.

The same clause does moving windows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row window that slides down the table, which with avg() is a three-period moving average.

Your turn

The editor holds six loans and a query that is meant to show each loan with the total taken in up to and including its date.

Press Run first. The running_total column shows 5600 on every row: that is the grand total, because OVER () means "all the rows".

Fill in those brackets so the window is ordered by taken_on and framed with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Run it again and the column should climb 950, 1450, 2650, 4150, 4500, 5600.

Then add PARTITION BY member inside the same brackets, before the ORDER BY, and watch the running total restart for each person.

If something goes wrong

If every row still shows 5600, the new clauses went outside the OVER brackets rather than inside them.

If you get near "ROWS": syntax error, the frame is in the wrong place — it comes after the window's ORDER BY, inside the same brackets.

If the last row is not 5600, one of the rows is being left out or counted twice; check that the frame starts at UNBOUNDED PRECEDING and ends at CURRENT ROW.

If you get misuse of window function sum() — or misuse of aliased window function running_total — the window function has ended up in a WHERE clause. Window functions are worked out after WHERE, so any filtering on one has to happen in an outer query or a CTE.

Nothing here can break. The table is rebuilt from scratch on every run.

Write your code

Runs in your browser. Press Run (or Ctrl/Cmd+Enter) and the output is checked for you.

Ctrl/Cmd+Enter to run

Press Esc then Tab to move keyboard focus out of the code editor.

Ready

Output will appear here after you run your code.

The runtime is starting in the background. You can type now — it will be ready before you are.

The answer appears here once you have run your code at least once.

Things that often go wrong here

Putting the ORDER BY outside the brackets: The query already ends with `ORDER BY taken_on`, and that one only decides the order the rows are printed in. The window has its own ORDER BY, inside the OVER brackets, and only that one affects what the sum adds up. Two ORDER BY clauses in one query looks odd and is correct.
Relying on the default frame: `sum(deposit) OVER (ORDER BY taken_on)` gives the right answer on this data because all six dates are different. The default frame is RANGE, in which "current row" means every row sharing the current ORDER BY value — so the day two loans land on the same date, both show the combined figure and the running total appears to skip a step. Writing ROWS makes each row see only the rows up to itself. This is standard behaviour in PostgreSQL and MySQL too.
Using GROUP BY instead: `SELECT taken_on, sum(deposit) FROM loans GROUP BY taken_on` collapses the rows: you lose the member and the individual deposit, and each date shows only its own total rather than the total so far. A window function computes across rows without removing any, which is the whole reason it exists.
Putting the window function in a WHERE clause: Filtering on a running total fails either way you write it: `WHERE running_total > 2000` gives `misuse of aliased window function running_total`, and repeating the whole expression gives `misuse of window function sum()`. Window functions are computed after WHERE has already run. Wrap the query in a CTE and filter outside it.
Ordering the window by something that is not in the SELECT list: It is legal — `OVER (ORDER BY id)` works fine — and it makes the output impossible to check, because the reader cannot see what the numbers are accumulating along. Order the window by a column you are showing, or say so in a comment.

Longer explanation: read the full lesson. Want a blank editor instead? Open the SQL playground.