Running totals and moving averages in SQL
SUM(x) OVER (ORDER BY day) adds up x from the first row to the current one: a running total. Window frames turn the same idea into moving averages.
Running total
Put ORDER BY inside OVER and the aggregate becomes cumulative. Add PARTITION BY to restart the total for each account or customer.
SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders WHERE status = 'completed';
Moving average with a frame
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current row and the two before it. The default frame is RANGE, which treats rows with the same ORDER BY value as one step, so ties are added together. Say ROWS when you mean rows.
SELECT order_date, amount, ROUND(AVG(amount) OVER (ORDER BY order_date, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg FROM orders WHERE status = 'completed';
Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- Forgetting ORDER BY inside OVER, which gives the grand total on every row.
- Relying on the default RANGE frame when dates repeat.
- No PARTITION BY when each account needs its own total.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.
- Running revenue totalSUM() OVER · running totalsPro
- Share of total spendSUM() OVER () · percent of totalPro
- 3-transaction moving averageWindow frames · ROWS vs RANGEPro
- First vs latest order valueFIRST_VALUE · LAST_VALUE · frame trapPro
- Customer share of country revenueSUM() OVER (PARTITION BY) · percent of groupPro
- 7-day moving average revenueRANGE window frame · calendar moving averagePro
- New vs returning usersFirst-seen month · running distinct countPro
- Sessions from raw pageviewsLAG · running SUM · sessionizationPro