Running Totals and Cumulative Metrics in SQL
A running total accumulates a metric across an ordered sequence of rows, so that each row carries the sum of all preceding rows plus its own value. Cumulative metrics extend this pattern: cumulative revenue, cumulative user signups, cumulative events. Both are produced by combining period-grouped aggregation with a window function that uses an expanding frame over the ordered result.
A running total accumulates a metric across an ordered sequence of rows so that each row carries the sum of all preceding rows plus its own value. Cumulative revenue, cumulative signups, cumulative events — all follow the same two-stage pattern: aggregate by period first, then accumulate across periods with a window function.
Your manager wants cumulative revenue for the year, showing how total revenue grows each day. The raw orders table has one row per order, not one per day. You need to aggregate to day first, then accumulate. Running the window function directly on the unaggregated table gives you a running total per transaction — one row per order, each carrying the sum of all prior orders. That answers a different question.
WITH daily_revenue AS (
SELECT
ordered_at::date AS order_date,
SUM(total_amount) AS revenue
FROM orders
GROUP BY ordered_at::date
)
SELECT
order_date,
revenue,
SUM(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM daily_revenue
ORDER BY order_dateThe CTE produces one row per day. The window function in the outer query accumulates revenue from the first day through each current day. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW expands the frame by exactly one row at each step — appropriate here because the data has one row per period after aggregation. If the pre-aggregated result ever had two rows with the same date, RANGE would treat them as a single frame boundary and give both rows the same cumulative total, while ROWS would treat them as separate steps. For pre-aggregated data with one row per period, the two behave identically.
The two-stage structure is deliberate
Applying the window function directly on the unaggregated table produces a running total at the individual transaction level, not the period level. "Cumulative revenue as of each month" requires aggregating to the month first. Skipping the aggregation step produces a running total that increments on every transaction, which answers a different question.
PARTITION BY for per-entity running totals
To compute cumulative revenue per region (not globally), add PARTITION BY:
SUM(revenue) OVER (
PARTITION BY region
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue_by_regionWithout PARTITION BY, the accumulation crosses region boundaries and produces a single global total.
The one thing that trips people up
Gaps in the data affect running totals. If the pre-aggregated data has missing days (no orders on that day, so no row), the running total jumps from the day before the gap to the day after. There's no row for the gap period to show a repeated cumulative total. When the requirement is a continuous series with every day represented, build a date spine first and zero-fill the missing days before running the accumulation.
10 Running Totals and Cumulative Metrics practice problems
Write a query to return each order day, the number of `orders` placed on that day, and the running total of all `orders` placed from the earliest day through that day.
Write a query to return each calendar month in which `users` signed up, the count of new `users` that month, and the running total of all `users` signed up from the earliest signup month through that month.
Write a query to return each order month, the total `orders` revenue for that month, and the running total of all `orders` revenue from the earliest month through that month.
Write a query to return each (`order_month`, `status`) combination, the count of `orders` with that `status` in that month, and the running total of `orders` in that `status` from the earliest such month through the current month — restarting at the earliest month for each `status` independently.
Write a query to return each signup month, the count of new `users` that month, and the rolling 3-month total — covering the current month and the two months immediately preceding it.
Write a query to return each order month in which at least one delivered order was placed, the total delivered revenue for that month, and the running total of delivered revenue from the earliest such month through that month.
Write a query to return each calendar month in which at least one salary became effective, the total salary `amount` that became effective in that month, and the running total of all salary commitments from the earliest such month through that month.
Write a query to return each order month, its calendar `order_year`, the revenue for that month, and the `ytd_revenue` — the running total from the start of that calendar year through that month, restarting at the start of each new year.
Write a query to return each order day, the average order value for that day alone, and the running average order value across every individual order placed from the earliest day through that day.
Write a query to return each order month, the revenue for that month, the cumulative revenue from the earliest month through that month, and the remaining revenue from that month through the latest month in the data.
These problems are part of the Running Totals and Cumulative Metrics lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.