Return
name (the customer's name)country (the customer's country)order_count (the number of distinct orders placed by the customer)total_spend (the sum of the customer's spending, calculated as quantity times unit price for all their order items)total_spend from highest to lowest.Expected output
| name | country | order_count | total_spend |
|---|---|---|---|
| 2 rows — values hidden, that's the puzzle | |||
Your query should return 2 rows with these columns. Your column aliases don't need to match.
customers
| id | name | country | created_on |
|---|---|---|---|
| 1 | Acme Co | US | 2022-01-10 |
| 2 | Bravo Ltd | UK | 2022-03-22 |
| 3 | Cirrus | US | 2022-05-01 |
| 4 | Delta | DE | 2023-02-14 |
| 5 | Echo | US | 2023-06-30 |
products
| id | name | category | price |
|---|---|---|---|
| 1 | Widget | Hardware | 9.99 |
| 2 | Gadget | Hardware | 19.5 |
| 3 | Manual | Books | 5 |
| 4 | Course | Digital | 49 |
| 5 | Sticker | Misc | 1.25 |
orders
| id | customer_id | ordered_on | status |
|---|---|---|---|
| 1 | 1 | 2023-01-05 | completed |
| 2 | 1 | 2023-02-11 | completed |
| 3 | 2 | 2023-02-15 | completed |
| 4 | 3 | 2023-03-01 | cancelled |
| 5 | 2 | 2023-04-20 | completed |
order_items
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 9.99 |
| 2 | 1 | 3 | 1 | 5 |
| 3 | 2 | 4 | 1 | 49 |
| 4 | 3 | 2 | 3 | 19.5 |
| 5 | 4 | 1 | 5 | 9.99 |
0 of 4 hints revealed
Submit an attempt first.