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 row

How it works

  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.

Key points

Common mistakes

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.