task Conditional aggregation · medium
Order status mix by region
Return region and four columns — delivered, shipped, processing, cancelled — counting the orders of shoppers in that region with each status.
what to return
- Columns
regiondeliveredshippedprocessingcancelledin this order- Rows
- 5 rows · any order
- Watch for
- Shoppers with no region are grouped under 'Unknown'.
- A region with no orders of a status shows 0.
- Skill
- Turn rows into columns with SUM(CASE …). Refresher: Connect tables with JOIN →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result5 rows · any order
| region | delivered | shipped | processing | cancelled |
|---|---|---|---|---|
| East | 8 | 0 | 0 | 0 |
| North | 2 | 1 | 1 | 1 |
| South | 4 | 1 | 0 | 0 |
first 3 of 5 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
sales_orders5 columns · 33 rows
| idinteger · key | shopper_idinteger → shoppers | order_datetext | statustext | delivered_datetext |
|---|---|---|---|---|
| 101 | 1 | 2025-01-04 | delivered | 2025-01-08 |
| 102 | 2 | 2025-01-06 | delivered | 2025-01-07 |
| 103 | 1 | 2025-01-15 | delivered | 2025-01-20 |
| 104 | 3 | 2025-01-18 | cancelled | NULL |
| 105 | 4 | 2025-01-22 | delivered | 2025-01-31 |
showing 5 of 33 rows
shoppers5 columns · 17 rows
| idinteger · key | nametext | emailtext | regiontext | signup_datetext |
|---|---|---|---|---|
| 1 | Ana Ruiz | ana@harbor.test | West | 2025-01-03 |
| 2 | Ben Okafor | ben@harbor.test | East | 2025-01-05 |
| 3 | Chloe Park | chloe@harbor.test | West | 2025-01-09 |
| 4 | Dmitri Volkov | dmitri@harbor.test | North | 2025-01-14 |
| 5 | Esi Mensah | NULL | South | 2025-01-20 |
showing 5 of 17 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.