Which Category Drives the Most Revenue?
Merchandising at Whetcode Supply is splitting next quarter's marketing budget between the parts of the catalogue, and wants to back the one already bringing in the most money.
Two things make this awkward. Money is not recorded on an order — an order line says how many units and which product, and the price sits on the product. And the thing being compared is not a product but a category, which several different products share: two separate items both count towards furniture, so their revenue has to end up in the same bucket.
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. category is the part of the catalogue the item belongs to.
| 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, category and revenue, for the category that has brought in the most money. A line of an order is worth its quantity multiplied by that product's price. Exactly one row comes back. No two Whetcode categories tie on this data, so there is no tie to settle.
Example output — shape only, on an invented category and total.
| category | revenue |
|---|---|
| lighting | 2340 |
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