Top customer in each city
advanced Window functions Challenge 12 of 13
The marketing team wants to reward the biggest spender in each city.
Requirements
- A customer’s spend is the sum of
quantity * unit_priceover their orders withstatus'paid'or'shipped', rounded to 2 decimal places. - Ignore customers with no city.
- Return
city,nameandtotal_spendfor the highest spender in each city. If several customers tie for the top, return all of them. - Order by
city, thenname.
Expected output
city | name | total_spend Leeds | Isla Reid | 367.97 London | Ava Patel | 322.48 Manchester | Finn Walsh | 467
Checks (3)
- ○Sample data
- ○Kara draws level with Finn
- ○Chloe's cancelled order is reinstated
Each check builds a fresh database and compares your result with the reference solution's: same number of columns in the same order (names are not checked), and the same rows in the same order.
query.sqlExecutes on SQLite in your browser
Ctrl/⌘ + Enter to check query · your code is saved in this browser
Results
Use Run query to see your rows, then Check query to grade it.
Schema · Acme Store
SQLiteLoading the database…
“null” marks columns that allow NULL. Foreign keys are not enforced, as in many real test databases.