94
Write a query to calculate a running total (cumulative sum) of daily sales
Level: Hard — A staple of analytics/reporting interviews; the window-function frame clause is the key detail.
Scenario: The finance dashboard needs a "cumulative revenue" column — each day should show that day's sales plus everything earned before it, so the last row equals the grand total. They also want it reset per month in a second version. How do you write it?
Sample Data — daily_sales
| sale_date | amount |
|---|
| 2026-01-01 | 100 |
| 2026-01-02 | 150 |
| 2026-01-03 | 200 |
| 2026-02-01 | 300 |
| 2026-02-02 | 250 |
Solution A — Running Total with a Window Function (MySQL 8.0+)
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales
ORDER BY sale_date;
| sale_date | amount | running_total |
|---|
| 2026-01-01 | 100 | 100 |
| 2026-01-02 | 150 | 250 |
| 2026-01-03 | 200 | 450 |
| 2026-02-01 | 300 | 750 |
| 2026-02-02 | 250 | 1000 |
The frame clause is critical: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells MySQL to sum from the first row up to the current one. Omitting it can give wrong results with ties, and the explicit frame also avoids the slower default RANGE behaviour.
Solution B — Running Total Reset Per Month (PARTITION BY)
SELECT
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY YEAR(sale_date), MONTH(sale_date) -- reset each month
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS monthly_running_total
FROM daily_sales
ORDER BY sale_date;
| sale_date | amount | monthly_running_total |
|---|
| 2026-01-01 | 100 | 100 |
| 2026-01-02 | 150 | 250 |
| 2026-01-03 | 200 | 450 |
| 2026-02-01 | 300 | 300 ← reset |
| 2026-02-02 | 250 | 550 |
Solution C — Without Window Functions (MySQL 5.7 self-join)
-- Correlated self-join: sum every row with a date <= current date
SELECT
d1.sale_date,
d1.amount,
(SELECT SUM(d2.amount)
FROM daily_sales d2
WHERE d2.sale_date <= d1.sale_date) AS running_total
FROM daily_sales d1
ORDER BY d1.sale_date;
-- Correct, but O(n^2) — slow on large tables. Prefer window functions.
Performance note: The self-join approach re-scans the table for every row (quadratic). On 1M rows it's disastrous. Window functions compute the running total in a single ordered pass — always prefer them on modern MySQL.
Interviewer follow-up: "Now give me a 7-day moving average instead of a cumulative total." → Change the frame to a sliding window: AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — the frame clause is what turns a running total into a moving average.