Multi-table joins & revenue
Follow a chain of tables
Lesson 3 of 20 · 10 min · Module 1: Joins beyond the basics
Business questions often cross several tables. Revenue per category needs the quantity and price from order_items, the category from products, and the status from orders.
Start this lesson →Free. No installation: you write and run the code in your browser, and it is checked automatically.
By the end, you’ll be able to…
- Join three tables through their keys
- Use the price stored with each order line
- Filter before aggregating
The idea
Each JOIN … ON adds one table through its key. Filter with WHERE once all the tables are available, and calculate line totals as quantity * unit_price_cents: the price paid at the time of the order, which may differ from today’s price.
SELECT o.id, p.name, oi.quantity
FROM order_items AS oi
JOIN orders AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.id = 5;
01Start from the most detailed table
order_items has one row per product per order. Starting there and joining outward keeps exactly one row per sold line, so the sums are right.
02Prices change, history does not
products.price_cents is today’s price. order_items.unit_price_cents is what the customer actually paid, including discounts. Revenue must use the stored price.
SELECT p.price_cents, oi.unit_price_cents
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id = 9 AND oi.product_id = 1;1200 | 110003IN keeps lists readable
status IN ('paid', 'shipped') means the same as two conditions joined with OR, and it is easier to extend.
Your turn
category and revenue_cents (sum of quantity × unit_price_cents) for orders whose status is paid or shipped. Sort by revenue, highest first.What the checks look for
- Totals revenue for Pantry, Kitchen and Office
Take this with you
Join from the detail table outward, and calculate with the values stored at the time.
Go a little further
Add a column with the number of units sold per category.