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;
Output
1200 | 1100

03IN keeps lists readable

status IN ('paid', 'shipped') means the same as two conditions joined with OR, and it is easier to extend.

Your turn

Return 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
Try it in the editor →

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.

Learn SQL Intermediate for free

20 short lessons with real code and instant checks.

See the whole course →