Running totals and moving averages

Why it matters

Daily numbers are noisy. A dashboard that shows "revenue so far this month" (a running total) or "7-day average sign-ups" (a moving average) tells a much clearer story than raw daily values. Before window functions, analysts did this with slow self-joins or in a spreadsheet. With aggregate window functions it is one line of SQL.

Aggregates as window functions

Any normal aggregate (SUM, AVG, COUNT, MIN, MAX) becomes a window function when you add OVER (...). With an empty window it sees every row:

SELECT sale_date, amount,
       SUM(amount) OVER () AS grand_total,
       ROUND(amount * 100.0 / SUM(amount) OVER (), 1) AS pct_of_total
FROM daily_sales;

Every row keeps its own amount and also gets the grand total, which makes "share of total" calculations easy. Add PARTITION BY store and each store gets its own total.

Running totals: add ORDER BY

When the window has an ORDER BY, the aggregate only looks at rows up to the current one:

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
ORDER BY sale_date;

Use PARTITION BY to restart the total, for example per month: SUM(amount) OVER (PARTITION BY substr(sale_date, 1, 7) ORDER BY sale_date) gives month-to-date revenue.

Window frames: exactly which rows count

The frame says which rows around the current one are included. You write it after ORDER BY:

ROWS BETWEEN <start> AND <end>

where start and end can be UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING.

A 3-day moving average is "this row and the two before it":

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

The first two rows average fewer than three values, because there are no earlier rows. If your report should only show complete windows, add COUNT(*) OVER (same window) and hide rows where it is below 3.

ROWS vs RANGE: the hidden default

When you write ORDER BY without a frame, the SQL standard default is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE treats rows with the same ORDER BY value as one step. If two sales share the same date, both rows get the total including both of them, so the running total "jumps" on ties. ROWS counts physical rows one at a time. For a running total on a column with possible duplicates, either:

• write the frame explicitly: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW plus a tie-breaker such as ORDER BY sale_date, id, or
• aggregate to one row per day first (in a CTE), then run the window over the daily totals.

The second option is usually what the business wants anyway.

Moving windows need complete data

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "the previous 6 rows", not "the previous 6 days". If a day with no sales is simply missing from the table, the window silently stretches over 8 or 9 calendar days. Fix gaps first: generate every date (a calendar table or the recursive CTE from the CTE lesson), LEFT JOIN your data onto it, and use COALESCE(amount, 0). PostgreSQL also supports RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW, which works on actual dates.

Common mistakes

• Empty OVER () when you wanted a running total. Without ORDER BY you get the grand total on every row.
• Relying on the default RANGE frame when the order column has ties.
• Moving averages over missing dates, which quietly cover the wrong period.
• Expecting the window ORDER BY to sort the output. It only defines the calculation. Add a final ORDER BY for the display order.
• Integer division when computing percentages: use 100.0, not 100, in SQLite and PostgreSQL.

Quick recap

• SUM(x) OVER () = grand total on each row; add PARTITION BY for per-group totals.
• SUM(x) OVER (ORDER BY d) = running total.
• Frames (ROWS BETWEEN n PRECEDING AND CURRENT ROW) define moving windows.
• Fill date gaps and use explicit ROWS frames to avoid surprises.

Try it

Open the Do tab and add a running total and a 3-day moving average to a daily sales table.