Running Total of Revenue by Date
Whetcode Supply sells desk gear online, and the founder is assembling the year-in-review deck. One revenue figure won't carry the slide — she wants the shape of the year: the line that climbs from the first order in February to the last one in June, so she can point at the stretch that actually paid for everything.
Two things make this awkward. An order records what was bought and how many, but never what it cost — money is a fact about a product and lives in the catalogue. And two orders both landed on 2023-02-01, so a phrase like "the money taken so far as of this order" only means something once you say which of those two came first.
products — the catalogue. price is what a single 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 — one row per order placed this year.
| 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 every order — all ten — with order_date, revenue (what that single order was worth: units bought times the unit price), and running_total, the revenue of every order up to and including this one, reading the orders oldest first and settling same-day ties by the smaller id first.
Example output — shape only, on an invented four-order ledger; the two orders on the same date show how the tie is settled.
| order_date | revenue | running_total |
|---|---|---|
| 2021-11-03 | 30 | 30 |
| 2021-11-03 | 45 | 75 |
| 2021-11-18 | 210 | 285 |
| 2021-12-02 | 63 | 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