8. Daily completed revenue
Asked in screens shaped like: Meta, Netflix, Spotify
Roll completed orders up to a calendar day. ordered_at is a timestamp, so grouping on it raw gives you one group per second instead of one per day.
Requirements
- Columns: order_day, revenue
- order_day is the date part of ordered_at (YYYY-MM-DD)
- Only status = 'completed'
- revenue rounded to 2 decimals, ordered by order_day
Expected output
order_day, 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
DATE(ordered_at) truncates the timestamp to a day in SQLite.
Hint 2
Group by the same expression you select.
Hint 3
Aggregate to the day before you attempt any window function on top.
SELECT DATE(ordered_at) AS order_day, ROUND(SUM(amount), 2) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE(ordered_at)
ORDER BY order_day;Why it works
Truncating the timestamp is the whole problem. In a warehouse this is DATE_TRUNC('day', ordered_at); the sandbox rewrites that to DATE() for you.
Run your query to see the result set.
Submit to run the checks.
Why it works
Truncating the timestamp is the whole problem. In a warehouse this is DATE_TRUNC('day', ordered_at); the sandbox rewrites that to DATE() for you.
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