D8LooPFocus modeCODE WORKSPACE
Dedupe staging before merge
Return country and avg_order_amount (AVG of total_amount, rounded to 2 decimals) grouped by buyer country. Replace a missing country with 'unknown'. Order by avg_order_amount descending, then country ascending.
JOIN customers, GROUP BY COALESCE(c.country, 'unknown'), ROUND(AVG(o.total_amount), 2), then ORDER BY avg_order_amount DESC, country ASC.
Sample input
ordersOrder amounts joined to their buyer (buyer_id → customers.customer_id).
| order_id | buyer_id | total_amount |
|---|---|---|
| 1001 | 1 | 80 |
| 1004 | 1 | 220 |
| 1009 | 1 | 30 |
| 1005 | 2 | 540 |
| 1011 | 2 | 110 |
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 outputAverage order amount per buyer country (missing country grouped as unknown), highest first.
| country | avg_order_amount |
|---|---|
| unknown | 325 |
| GB | 198 |
| US | 110 |
| IN | 95 |
Constraints
JOIN orders to customers on customers.customer_id = orders.buyer_id. Group by COALESCE(country, 'unknown'). Use ROUND(AVG(total_amount), 2). Order by avg_order_amount DESC, country ASC.
Expected skills
Joining then aggregating, grouping on a COALESCE expression, and AVG.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.