Running totals
Finance is closing out the books and wants a simple line for the report: revenue by day, and — running alongside it — how much has accumulated so far by each day. Not the total at the end. The total-up-to-here, for every single day.
Can we get a running total of revenue by day?
Is this another one of those subquery-per-row things? Sum up every order placed on or before this day?
It could be, and it would work — but you've already got a better tool for exactly this. You just haven't pointed it at ORDER BY yet.
Before any running total, we need one row per day with that day’s revenue. That’s a plain GROUP BY — nothing new — and since two completed orders happened to land on the same day (2023-03-01), grouping is what keeps that day as a single, correct number instead of two separate rows fighting over the same date.
SELECT placed_at, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY placed_at
ORDER BY placed_at;| placed_at | revenue |
|---|---|
| 2022-12-05 | 199.99 |
| 2023-01-10 | 34.5 |
| 2023-02-01 | 79.99 |
| 2023-02-10 | 49.99 |
| 2023-03-01 | 224.98 |
| 2023-03-14 | 49.98 |
Same tool as before — SUM() OVER (...) — but this time the window only needs an ORDER BY, no PARTITION BY. Ordered by date with no explicit frame, SQL defaults a window aggregate to “every row from the start, up through the current one” — which is precisely what a running total means. Since GROUP BY already collapsed the data down to one row per day, this reads cleanly as a CTE feeding the window function:
How much has added up by the time we reach this row?
WITH daily AS (
SELECT placed_at, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY placed_at
)
SELECT placed_at, revenue,
SUM(revenue) OVER (ORDER BY placed_at) AS running_total
FROM daily
ORDER BY placed_at;| placed_at | revenue | running_total |
|---|---|---|
| 2022-12-05 | 199.99 | 199.99 |
| 2023-01-10 | 34.5 | 234.49 |
| 2023-02-01 | 79.99 | 314.48 |
| 2023-02-10 | 49.99 | 364.47 |
| 2023-03-01 | 224.98 | 589.45 |
| 2023-03-14 | 49.98 | 639.43 |
Each running_total is just the previous running total plus that day’s own revenue. By 2023-03-01, NOVA had made 589.45 total since 2022-12-05 — and every day after keeps building on that same number.
Running…
All 17 days, ending at NOVA’s total completed revenue across the whole period. That last row’s running_total is just SUM(amount) over everything — a running total always ends exactly where a plain total would.
Try it: What if you run the window function directly on orders, without grouping by day first?
Run your query to see results here.
Look closely at the two rows dated 2023-03-01. You might expect them to show two different running totals, one after the other — but they come back identical. That’s because, with no explicit frame, the default window doesn’t just mean “every row before this one” — it means every row up through everything that ties with the current row’s ORDER BY value. Two orders share the exact same date, so SQL treats them as arriving together, and both get the running total as of the end of that day — 589.45. Group by day first, the way the lesson did, and this ambiguity disappears entirely: there’s only one row per date, so there’s nothing left to tie.
Finance now wants the same report, but without filtering by status — every order counts this time, not just completed ones. Return placed_at, revenue (the day’s total amount), and running_total, ordered by placed_at.
Hint 1
You already have the exact shape of this from the lesson — group by day first, then run a window SUM over that.
Hint 2
This time don't filter by status at all — every order counts, whatever state it's in.
Hint 3
WITH daily AS (SELECT placed_at, SUM(amount) AS revenue FROM orders GROUP BY placed_at) SELECT placed_at, revenue, SUM(revenue) OVER (ORDER BY placed_at) AS running_total FROM daily ORDER BY placed_at;
Solution
Running…
A window aggregate with ORDER BY and no explicit frame always defaults to “everything from the start up through here” — that default is what makes a running total just work, with no special syntax for it. The one sharp edge is ties: rows with the same ORDER BY value get treated as arriving together, which is exactly why grouping first, so each ordering value is unique, kept this report predictable.
SUM() OVER (ORDER BY ...) with no frame clause defaults to "everything up to and including this row" — that’s a running total. Watch for ties in ORDER BY: rows with equal values are treated as peers and get the same running total, which is why grouping first often keeps things predictable.