The Big Spenders
The loyalty programme at Whetcode Supply puts customers into tiers by what they have spent with the shop over their lifetime, and the tiers are assigned off a single leaderboard: everyone who has ever bought, biggest spender at the top.
All three tables are involved and none of them is optional. The customer table has the name to print. The order table has who bought what and how many. The catalogue has the only copy of the price. A row of this leaderboard needs one fact from each.
customers — one row per registered customer. city is the city given at signup and signup_date is the day they registered, written year-month-day.
| 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 — the catalogue, one row per item on sale. price is what a single unit costs, in dollars.
| 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 — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.
| 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 already exist in the database — there is nothing to create or load.
Task: Write a query returning two columns, name (the customer's name) and total_spend (everything that customer has ever spent, each of their orders being worth its item's price times its number of units, with the customer's total rounded to two decimal places), one row per customer who has placed at least one order, biggest spender first. No two customers tie. A registered customer who has never ordered does not appear at all.
Example output — shape only, on invented customers and totals:
| name | total_spend |
|---|---|
| Rosa Delgado | 1480 |
| Kenji Aoki | 990 |
| Bram Visser | 615 |
| Yara Osei | 340 |
| Milo Fontaine | 125 |
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