13. Revenue share by country
Asked in screens shaped like: Amazon, Shopify, Meta
Each country needs its percentage of total completed revenue on the same row as its own revenue. A second query for the grand total works but scans the table twice — do it with one window instead.
Requirements
- Columns: country, revenue, pct_of_total
- Only status = 'completed'
- pct_of_total is revenue / total revenue as a percentage, rounded to 2 decimals
- revenue rounded to 2 decimals, ordered by revenue descending
Expected output
country, revenue, pct_of_total
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
Aggregate per country in a CTE first.
Hint 2
SUM(revenue) OVER () with an empty OVER covers every row in the result.
Hint 3
Multiply by 100.0, not 100, to stay in floating point.
WITH by_country AS (
SELECT country, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY country
)
SELECT country,
ROUND(revenue, 2) AS revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM by_country
ORDER BY revenue DESC;Why it works
An empty OVER () makes the whole result set one partition, so you get the grand total on every row. The percentages sum to 100 apart from rounding.
Run your query to see the result set.
Submit to run the checks.
Why it works
An empty OVER () makes the whole result set one partition, so you get the grand total on every row. The percentages sum to 100 apart from rounding.
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