category, based on the total value of their purchases (quantity multiplied by unit price).Return
category: the product categorycustomer_name: the name of the top-spending customer in that categorytotal_spent: the total amount spent by that customer in the categorycategory (A-Z), then by customer_name (A-Z).Expected output
| category | customer_name | total_spent |
|---|---|---|
| Books | Acme Co | 5 |
| Digital | Acme Co | 49 |
| Digital | Delta | 49 |
First 3 of 5 rows your query should return. 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.