Running totals and moving averages: window frames

Step 1 Understand it

Why you need it. "Cumulative revenue by day", "7-day moving average of sign-ups" — growth charts are built on these. They also hide a classic trap: the default frame treats tied rows as one, which surprises almost everyone.

Watch it happen

Picture it

flowchart LR
  R1["row 1<br/>120"] --> R2["row 2<br/>80"] --> R3["row 3<br/>45"] --> R4["row 4<br/>200"]
  R4 --> F["frame for row 4:<br/>ROWS 2 PRECEDING = rows 2, 3, 4<br/>moving average 108.3"]
  R4 --> T["frame for row 4:<br/>UNBOUNDED PRECEDING = rows 1 to 4<br/>running total 445"]

The idea

SUM(amount) OVER (ORDER BY order_date) adds up everything from the first row to the current row: a running total. The frame says which rows count: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means this row and the two before it — so AVG over it is a 3-row moving average. The trap: when you write ORDER BY without a frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes every row tied with the current one. So two orders on the same day both show the total after both. Write ROWS explicitly when you want one row at a time.

The shape of it

SUM(x) OVER (ORDER BY d)                                   -- running total
AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)          -- running total, row by row

Words in lower case are the parts you fill in.

How it works, step by step

1. Order the rows by date. For each row, the frame is "from the first row up to me".
2. SUM over that frame grows: 120, 200, 245, 445, 595…
3. Order 106's NULL amount adds nothing, so the total stays at 595 on that row.
4. With ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, the frame is at most 3 rows, so AVG gives a 3-order moving average.
5. Ties: ordering salaries with the default frame, Ben and Chen (both 70000) both show 315000. With ROWS they show 245000 and 315000.

Analogy

A running total is a savings jar you never empty: each week you add the new amount and read the total. A moving average is a "last three weeks" window you slide along: the oldest week drops out as a new one comes in.

Where the analogy breaks

Weeks never tie. Rows can: with the default RANGE frame, two rows with the same ORDER BY value are treated as arriving at the same moment, so both see the total including each other.

Worked examples

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_running_total
FROM orders
WHERE customer_id IN (1, 3)
ORDER BY customer_id, order_date;

SELECT order_date, amount,
       ROUND(AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3
FROM orders
ORDER BY order_date;

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary DESC) AS default_range,
       SUM(salary) OVER (ORDER BY salary DESC, name ROWS UNBOUNDED PRECEDING) AS rows_frame
FROM employees
ORDER BY salary DESC, name;

Must-know points

• Aggregate + OVER (ORDER BY …) = a running total (or running count, max…).
• The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — tied rows are included together.
• Write ROWS explicitly for row-by-row running totals and for moving averages.
• ROWS BETWEEN n PRECEDING AND CURRENT ROW = the last n + 1 rows.
• Without ORDER BY in OVER, the frame is the whole partition, so you get the total, not a running total.

Common mistakes

• Getting the same running total on tied rows and not knowing why (the RANGE default).
• A "7-day" moving average with ROWS 6 PRECEDING when some days are missing — that is 7 rows, not 7 days.
• Forgetting PARTITION BY, so one customer's running total continues into the next customer's.

In other databases

• PostgreSQL, SQLite and Oracle also allow RANGE with an offset for real time windows: RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW (PostgreSQL).
• SQL Server supports ROWS and RANGE frames, but RANGE only with UNBOUNDED or CURRENT ROW.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  SQLite docs — Window Functions (frames)

  official documentation, free

• Read

  PostgreSQL docs — Window Function Calls (frame clause)

  official documentation, free

• Read

  Mode SQL Tutorial — SQL Window Functions: running totals

  Mode, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the RANGE default and why it gives Ben and Chen the same running total.

   Show answer

   When OVER has an ORDER BY but no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, not rows: it means "every row whose ORDER BY value is up to and including mine", and that includes rows that tie with me (my peers).

   Ben and Chen both earn 70,000, so they are peers. For Ben, the frame already contains Chen, and for Chen it contains Ben. Both get the same total, 315,000 (95 + 80 + 70 + 70 thousand), instead of 245,000 then 315,000.

   To count strictly one row at a time, say ROWS (ROWS UNBOUNDED PRECEDING) and add a tie-breaker to the ORDER BY, so the order of the tied rows is fixed.

2. Writefrom a blank page, then run it

   Write a running count of employees by hire_date (1 for the first hire, 2 for the second…).

   Show answer

   COUNT(*) OVER (ORDER BY hire_date) counts the rows from the start up to this one. No two people were hired on the same day, so the RANGE default is fine here. If two could share a date, use ROW_NUMBER (or ROWS with a tie-breaker), or both would get the same count.

   sqlCopy

   SELECT name, hire_date,
          COUNT(*) OVER (ORDER BY hire_date) AS hires_so_far
   FROM employees
   ORDER BY hire_date;

   Try it

   8 rows

   name	hire_date	hires_so_far
   Asha	2019-03-01	1
   Dara	2020-02-20	2
   Ben	2020-06-15	3
   Chen	2021-01-10	4
   Fatima	2021-09-12	5
   Eli	2022-07-01	6
   Hana	2022-11-30	7
   Gus	2023-04-03	8

3. Predictsay it before you run it

   In the moving-average example, what would the frame ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING give for 2024-01-07?

   Show answer

   81.7. The frame is the row before, this row and the row after: 120 (6 Jan), 80 (7 Jan) and 45 (8 Jan). (120 + 80 + 45) ÷ 3 = 81.67, rounded to 81.7. This is a centred average rather than a trailing one.

   Note 2024-02-12, order 106: its amount is NULL, and AVG skips it, so that row averages only its two neighbours: (150 + 60) ÷ 2 = 105.

   sqlCopy

   SELECT order_date, amount,
          ROUND(AVG(amount) OVER (
            ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
          ), 1) AS centred_avg_3
   FROM orders
   ORDER BY order_date;

   Try it

   10 rows

   order_date	amount	centred_avg_3
   2024-01-06	120	100
   2024-01-07	80	81.7
   2024-01-08	45	108.3
   2024-01-21	200	131.7
   2024-02-02	150	175
   2024-02-12	NULL	105
   2024-02-20	60	67.5
   2024-03-01	75	55
   2024-03-10	30	46
   2024-03-16	33	31.5

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Make Lena's running total show 0 instead of NULL on her first order, and explain where the COALESCE goes.

   Show answer

   Lena's first order (106) has a NULL amount, and a SUM over nothing but a NULL is NULL. The COALESCE goes around the whole window function, outside the OVER: COALESCE(SUM(amount) OVER (…), 0). It only steps in when the running total itself is NULL.

   SUM(COALESCE(amount, 0)) OVER (…) also works here. It turns each missing amount into 0 before adding. The difference shows only in meaning: the first says "no total yet, show 0"; the second says "a missing amount counts as £0".

   Putting a COALESCE inside the OVER brackets wouldn't help: what is in there only decides which rows are in each window and in what order, not the values that are added up.

   sqlCopy

   SELECT customer_id, order_date, amount,
          COALESCE(SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date), 0) AS customer_running_total
   FROM orders
   WHERE customer_id IN (1, 3)
   ORDER BY customer_id, order_date;

   Try it

   7 rows

   customer_id	order_date	amount	customer_running_total
   1	2024-01-06	120	120
   1	2024-01-07	80	200
   1	2024-01-08	45	245
   1	2024-02-02	150	395
   1	2024-03-10	30	425
   3	2024-02-12	NULL	0
   3	2024-02-20	60	60

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

Adding ORDER BY to an aggregate's OVER clause makes it cumulative, which gives running totals; a frame clause like ROWS BETWEEN 2 PRECEDING AND CURRENT ROW narrows it to a sliding window for moving averages. The subtle point is the default frame, RANGE up to the current row, which lumps tied ORDER BY values together — so I specify ROWS explicitly, with a unique tiebreaker, whenever ties are possible.

Follow-up questions

Tick each check when you can do it without notes