The Topline Number
The founder of Whetcode Supply asks the question every founder asks, usually the evening before a board call: how much money has this shop taken in altogether? One number, covering every order ever placed.
It is not stored anywhere. No row records what an order was worth — an order says which item and how many, and what that item costs lives in the catalogue. The single figure has to be built out of both tables.
orders — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.
| 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 — the catalogue, one row per item on sale. price is what a single unit costs, 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 |
The tables already exist in the database — there is nothing to create or load.
Task: Write a query returning one row with a single column, total_revenue, holding the value of every order added together, each order being worth its item's price times its number of units, with the finished total rounded to two decimal places.
Example output — the shape, on an invented figure. A shop that had taken five thousand two hundred and eighteen dollars seventy-five cents reads:
| total_revenue |
|---|
| 5218.75 |
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