Total Revenue per Product
The founder of Whetcode Supply wants to know which products earn their shelf space — not how many units moved, but how many dollars each product actually brought in over the year.
No single row holds that figure. An order records what was bought and how many, never what it cost: money lives only in the catalogue. And a product bought in several separate orders has its takings spread across those rows, so the per-product number has to be built rather than read off.
products — the catalogue. price is the cost 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 |
orders — one row per order placed this year. quantity is how many units that order was for.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning two columns, product_name and total_revenue, with one row per product that was bought at least once. Rows come back in alphabetical order of product_name.
Example output — shape only; the figures below are invented, not this data's answer.
| product_name | total_revenue |
|---|---|
| Cable Organiser | 96 |
| Monitor Arm | 540 |
| Paper Tray | 210 |
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