1. Completed revenue by country
Asked in screens shaped like: Stripe, Shopify, Square
Finance wants revenue by country, but only for orders that actually completed. Cancelled orders must not contribute a cent. Return one row per country with revenue rounded to 2 decimals, biggest country first.
Requirements
- Columns: country, revenue
- Only status = 'completed' rows count
- revenue is ROUND(SUM(amount), 2)
- Order by revenue descending
Expected output
country, 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
Filter before you aggregate — WHERE runs before GROUP BY.
Hint 2
One group per country, then SUM(amount).
SELECT country, ROUND(SUM(amount), 2) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY country
ORDER BY revenue DESC;Why it works
The filter has to land in WHERE, not HAVING — HAVING runs after grouping, so cancelled rows would already be inside the SUM. Rounding at the end keeps the cents from drifting.
Run your query to see the result set.
Submit to run the checks.
Why it works
The filter has to land in WHERE, not HAVING — HAVING runs after grouping, so cancelled rows would already be inside the SUM. Rounding at the end keeps the cents from drifting.
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