Return
country: the customer's countrycustomer_name: the customer's nameorder_id: the order's IDtotal_spent: the total amount spent by the customer across all orderscountry_rank: the customer's rank within their country by total spend (1 = highest)country and then customer_name.Expected output
| country | customer_name | order_id | total_spent | country_rank |
|---|---|---|---|---|
| DE | Delta | 6 | 49 | 1 |
| UK | Bravo Ltd | 3 | 110 | 1 |
| UK | Bravo Ltd | 5 | 110 | 1 |
First 3 of 6 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.