Cumulative Revenue Percentage by Product
The Whetcode Supply founder has a suspicion she wants tested: that the shop is really a one-product business wearing a five-product coat. The standard way to check is to lay the catalogue out from best seller to worst and read down it, asking at each step how much of the year's money has been accounted for so far. If you are past 80% by the second line, the range is far narrower than it looks from the shelf.
An order row is a line item — a product, a quantity, a date — and the price lives in the catalogue, so a product's revenue is spread across several rows before it is a number at all.
products — price is what one unit costs.
| 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
| 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 are already created and filled in the editor — there is nothing to read from input.
Task: Write a query that returns one row for each product that has ever sold, from the highest earner down to the lowest, with product_name, revenue (that product's money across all of its orders) and cumulative_pct — the percentage of the shop's entire year of revenue accounted for by this product together with every product above it in the list, rounded to 2 decimals. The last row therefore reads 100. A product nobody ever ordered does not appear.
Example output — shape only, on an invented three-product shop. Whatever the numbers, the last row always reads 100.
| product_name | revenue | cumulative_pct |
|---|---|---|
| Monitor Arm | 480 | 60 |
| Cable Organiser | 220 | 87.5 |
| Paper Tray | 100 | 100 |
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