mediumWindow functionsGROUP BY and aggregatesSQL
Daily Sales Running Total
Total the sales for each day, then add a running (cumulative) total across days with SUM() OVER. A practical window-function question for SQL interviews.
SQL
The table sales(id, sale_date, amount) has one row per sale; a day can have several sales. Dates are stored as YYYY-MM-DD.
Write a query that returns one row per day that has sales, with three columns in this order: sale_date, daily_total (the sum of that day's amounts) and running_total (the sum of all amounts up to and including that day). Order the rows by sale_date, oldest first.
Examples
Example 1
Input: sales: (1, 2026-03-01, 100), (2, 2026-03-01, 50), (3, 2026-03-02, 30), (4, 2026-03-04, 20) Output: sale_date | daily_total | running_total 2026-03-01 | 150 | 150 2026-03-02 | 30 | 180 2026-03-04 | 20 | 200 Why: Days without sales (2026-03-03) don't appear; the running total carries on from the previous day that had sales.
Constraints
- Order by sale_date ascending.
- Only days that have at least one sale appear.
- amount is a positive whole number.
Hints
Hint 1
First collapse the sales to one row per day with GROUP BY sale_date.
Hint 2
A window function can run over the grouped rows: SUM(...) OVER (ORDER BY sale_date).
Hint 3
You can put an aggregate inside a window function: SUM(SUM(amount)) OVER (...).
Solution
Show the solution (try it yourself first)
Approach: group by day, then a window sum
Group the sales by day to get each day's total. Window functions are evaluated after GROUP BY, so a SUM window ordered by date can run over the grouped rows and add up everything so far. A CTE keeps the two steps easy to read.
WITH daily AS (
SELECT sale_date, SUM(amount) AS daily_total
FROM sales
GROUP BY sale_date
)
SELECT sale_date, daily_total,
SUM(daily_total) OVER (ORDER BY sale_date) AS running_total
FROM daily
ORDER BY sale_date;The same thing in one step: SUM(SUM(amount)) OVER (ORDER BY sale_date). With ORDER BY inside OVER, the default frame runs from the first row to the current one, which is exactly a running total. Before MySQL 8 people used a correlated subquery per day, which is O(n²).
Complexity
O(n log n): one grouping sort and one ordered pass for the window.
Found a mistake in this problem, its tests or the solution?