Running totals and moving averages: window frames
With ORDER BY inside OVER, SUM and AVG become running totals; a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the window to nearby rows, giving a moving average.
Why it matters
"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.
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.
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.
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.
Syntax
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 rowHow it works
- Order the rows by date. For each row, the frame is "from the first row up to me".
- SUM over that frame grows: 120, 200, 245, 445, 595…
- Order 106's NULL amount adds nothing, so the total stays at 595 on that row.
- With
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, the frame is at most 3 rows, so AVG gives a 3-order moving average. - Ties: ordering salaries with the default frame, Ben and Chen (both 70000) both show 315000. With ROWS they show 245000 and 315000.
Key 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 ROWis a frame of the last n + 1 rows: the current row and the n before it.- 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.
Not true: ROWS 6 PRECEDING covers the last 7 days, even when some days have no row.
In fact: It covers the last 7 rows. If some days have no row, those 7 rows reach further back than 7 days, so check for missing days first.
In the interview
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.
Part of Unit 5: Window functions: ranking, running totals and comparing rows in the free SQL course.