D8LooPFocus modeCODE WORKSPACE
Revenue by buyer country
Return country and revenue (the SUM of orders.total_amount) grouped by buyer country. Replace a missing country with 'unknown'. Order by revenue descending, then country ascending.
JOIN customers, GROUP BY COALESCE(c.country, 'unknown'), SUM(o.total_amount), then ORDER BY revenue DESC, country ASC.
Sample input
ordersAll 12 orders (buyer_id and total_amount shown).
| order_id | buyer_id | total_amount |
|---|---|---|
| 1001 | 1 | 80 |
| 1004 | 1 | 220 |
| 1005 | 2 | 540 |
| 1010 | 6 | 640 |
customersCustomer 2 has a NULL country, which becomes the unknown bucket.
| customer_id | country |
|---|---|
| 1 | US |
| 2 | null |
| 3 | GB |
| 4 | IN |
| 6 | GB |
Expected output
Expected outputRevenue summed per buyer country (missing country grouped as unknown), highest first.
| country | revenue |
|---|---|
| GB | 990 |
| unknown | 650 |
| US | 330 |
| IN | 190 |
Constraints
JOIN orders to customers on customers.customer_id = orders.buyer_id. Group by COALESCE(country, 'unknown') so the missing country forms its own bucket.
Expected skills
Joining then aggregating, grouping on a COALESCE expression, and deterministic ordering of grouped results.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.