What Did Our Revenue Curve Look Like?
Finance at Whetcode Supply wants the revenue curve for the year so far — not the total, which they already have, but the shape: how the money arrived, one order at a time, so that a flat stretch or a sudden step is visible on the page.
Two things make this less simple than it sounds. An order row records how many units were bought but not what they cost — the unit price lives on the product. And two orders were both placed on 2023-02-01, so "the order before this one" is ambiguous on that date unless something else settles it.
orders — one row per order. quantity is 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 product sold. 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 two columns, id and running_revenue. For an order, running_revenue is the money taken by that order together with every order that came before it, counting orders from oldest date to newest and settling a same-date tie by the smaller id first. The money an order brings in is its quantity multiplied by the unit price of the product it is for. Amounts are exact dollars and are not rounded. Every order comes back — ten rows in, ten rows out. Rows come back sorted by id, smallest first.
Example output — shape only, on an invented four-order ledger.
| id | running_revenue |
|---|---|
| 1 | 30 |
| 2 | 75 |
| 3 | 285 |
| 4 | 348 |
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