Running totals and moving averages

Add ORDER BY and a frame inside OVER to build year-to-date totals and smooth out bumpy numbers.

Lesson 29’s windows gave every row the same group total. Put an ORDER BY inside OVER, and the window changes: each row sees only the rows up to itself. That’s a running total.

Running total

WITH monthly AS (
    SELECT strftime('%Y-%m', closed_at) AS month,
           SUM(amount) AS won
    FROM opportunities
    WHERE is_won = 1
      AND closed_at >= '2026-01-01'
      AND closed_at <  '2027-01-01'
    GROUP BY month
)
SELECT month,
       won,
       SUM(won) OVER (ORDER BY month) AS year_to_date
FROM monthly;

The last row equals the year’s total, $650,500, which is a quick way to check it.

Add PARTITION BY, and the running total restarts for each group. Each rep’s career won revenue, deal by deal:

SELECT owner_id,
       closed_at,
       amount,
       SUM(amount) OVER (PARTITION BY owner_id ORDER BY closed_at) AS running_won
FROM opportunities
WHERE is_won = 1;

The frame: which rows the window covers

The frame says exactly which rows each calculation includes. You can write it out:

SUM(won) OVER (ORDER BY month
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Moving average

A three-month moving average covers this month and the two before it:

WITH monthly AS (
    SELECT strftime('%Y-%m', closed_at) AS month,
           SUM(amount) AS won
    FROM opportunities
    WHERE is_won = 1
      AND closed_at >= '2026-01-01'
      AND closed_at <  '2027-01-01'
    GROUP BY month
)
SELECT month,
       won,
       ROUND(AVG(won) OVER (ORDER BY month
                            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS moving_avg_3
FROM monthly;

June’s won revenue was $285,000, but its three-row average is $143,333, which smooths out one big month. The first two rows average fewer than three values, because there’s nothing before them.

The same gap from lesson 31 applies: these are three rows, not three calendar months. With May missing, June’s window covers March, April and June. Fill the missing months first (lesson 34) for a true three-month average.

The trap: ties and the default frame

When you write ORDER BY but no frame, the default frame is RANGE ... CURRENT ROW. RANGE treats rows with the same ORDER BY value as one step, so tied rows all get the same running total.

Order deals by the month they were created, and the difference shows up:

SELECT name,
       strftime('%Y-%m', created_at) AS month,
       amount,
       SUM(amount) OVER (ORDER BY strftime('%Y-%m', created_at)) AS default_frame,
       SUM(amount) OVER (ORDER BY strftime('%Y-%m', created_at)
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM opportunities
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-03-01';

All four January deals tie on month, so the default frame jumps straight to the January total. ROWS adds them one at a time. Neither is wrong, but if you want a true row-by-row running total, write ROWS and add a tiebreaker to the ORDER BY, like ORDER BY created_at, opportunity_id.

Common mistakes

Dialect notes

SQL Server, PostgreSQL, MySQL 8.0+ and SQLite all support ROWS frames and the same default. PostgreSQL and MySQL also accept time-based frames like RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW. SQLite accepts numeric offsets only. SQL Server’s RANGE only works with UNBOUNDED and CURRENT ROW.

Snowflake notes

Snowflake uses the same default frame for SUM, AVG and the other aggregates, so the tie trap applies there too. It also supports RANGE frames with explicit offsets, like RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW on a timestamp, for windows measured in time rather than rows. That needs exactly one ORDER BY column. One difference: FIRST_VALUE and LAST_VALUE default to the whole window, which isn’t the ANSI standard. Running totals and moving averages are common dbt metrics, usually built on a date spine (lesson 34) so missing days don’t skew them.

More in the field guide: Running total & moving average. Exercise A4 is a per-rep running total.

Try it

Show won revenue for each month of 2026 that had wins, with a running year-to-date total beside it, in month order. Return the month as YYYY-MM, that month’s won revenue and the running total.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson