Top Spending Customer per City
Whetcode Supply is sending a hand-written thank-you note to its best customer in every city it ships to, and marketing needs the list of who gets one.
Nothing in the database stores "best customer". Spend is scattered over order rows, and an order row doesn't even hold money — it holds a product and a quantity, with the price waiting over in the catalogue. So the leaderboard has to exist in full before the winner of any city can be picked off it: you cannot tell whether Wei Zhang beats Priya Nair in Austin until both of their yearly totals are finished.
customers
| id | name | city | signup_date |
|---|---|---|---|
| 1 | Priya Nair | Austin | 2022-01-15 |
| 2 | Tom Becker | Berlin | 2022-03-02 |
| 3 | Sofia Rossi | Milan | 2022-05-19 |
| 4 | Liam OConnor | Dublin | 2023-01-08 |
| 5 | Wei Zhang | Austin | 2023-04-27 |
products — price is per unit.
| id | name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | electronics | 25.0 |
| 2 | Standing Desk | furniture | 350.0 |
| 3 | Desk Lamp | furniture | 40.0 |
| 4 | Mechanical Keyboard | electronics | 85.0 |
| 5 | Notebook Set | office | 12.0 |
orders
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
The tables are already created and filled in the editor — there is nothing to read from input.
Task: Write a query that returns name, city and total_spend for the biggest spender in each city, taking a customer's spend to be units bought times unit price added up over every order they placed. If two customers in the same city are level at the top, return both of them. A city nobody has ordered anything from does not appear at all. Hand the winners back in alphabetical city sequence, and by name inside a city that produced two of them.
Example output — shape only, on invented cities and customers; the two rows for Osaka show a city whose top spenders are level.
| name | city | total_spend |
|---|---|---|
| Dana Reyes | Bogota | 512 |
| Ola Berg | Lisbon | 96 |
| Nadia Haq | Osaka | 240 |
| Rune Dahl | Osaka | 240 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in