10. Running total of daily revenue
Asked in screens shaped like: Stripe, Airbnb, Booking
Finance wants a cumulative revenue column beside the daily number so they can see the month build up. Aggregate to a day first, then run the window over that result — not over raw orders.
Requirements
- Columns: order_day, revenue, running_revenue
- Only status = 'completed'
- running_revenue is the cumulative sum of revenue ordered by day
- Both money columns rounded to 2 decimals, ordered by order_day
Expected output
order_day, revenue, running_revenue
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
Do the daily GROUP BY inside a CTE.
Hint 2
SUM(revenue) OVER (ORDER BY order_day) is cumulative by default.
Hint 3
An ORDER BY inside OVER implies the frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
WITH daily AS (
SELECT DATE(ordered_at) AS order_day, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE(ordered_at)
)
SELECT order_day,
ROUND(revenue, 2) AS revenue,
ROUND(SUM(revenue) OVER (ORDER BY order_day), 2) AS running_revenue
FROM daily
ORDER BY order_day;Why it works
Windows run after GROUP BY, which is exactly why the two-step CTE works. Adding ORDER BY inside OVER switches SUM from a whole-partition total to a running total.
Run your query to see the result set.
Submit to run the checks.
Why it works
Windows run after GROUP BY, which is exactly why the two-step CTE works. Adding ORDER BY inside OVER switches SUM from a whole-partition total to a running total.
Pro problem
Medium problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems