Who's Our Highest-Value Customer?
Whetcode Supply is launching a VIP programme and wants to send the very first invitation to its single biggest-spending customer.
The order log will not tell you what an order was worth. An order row records how many units were bought and which product they were, but the money lives on the product: orders has no price column at all, and products has no idea who bought anything. Neither table can answer the question by itself, and a customer who placed three small orders may well beat one who placed a single large one.
orders — one row per order line. quantity is the number of units bought; product_id points at a row in products.
| 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 |
products — one row per item on sale. price is the price of a single unit, 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single row with two columns, customer_id and total_spent, for the customer whose orders add up to the most money. An order is worth its quantity multiplied by that product's price. Exactly one row comes back. No two Whetcode customers tie on this data, so there is no tie to settle.
Example output — shape only, on an invented customer and total.
| customer_id | total_spent |
|---|---|
| 8 | 1275 |
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