Level 1 · Revenue by category

A join lines up rows from two tables that match on a condition. Each order line has a product_id, and each product has one row in products, so you can attach the product’s category to every line:

SELECT i.order_item_id, i.quantity, p.name, p.category
FROM order_items AS i
JOIN products AS p ON p.product_id = i.product_id;

JOIN on its own means INNER JOIN: only rows that find a match on both sides are kept. An order line whose product is missing from products would disappear. At Harbor & Pine every line has a valid product, but in real warehouses that is never guaranteed, which is why the next level matters.

Aliases and qualified names

order_items AS i gives the table a short name. Once a query touches several tables, prefix every column with its table alias (i.quantity, p.category). It is required when both tables have a column of the same name. Here both have unit_price: i.unit_price is the price paid, while p.unit_price is today’s list price. Using the wrong one is a real and common revenue bug. Writing unit_price without a prefix fails with an “ambiguous column” error, which is the database doing you a favor.

Join, then aggregate

Joins happen first (they are part of FROM), then WHERE, then GROUP BY. So you can filter and group on columns from either table:

SELECT p.category,
       ROUND(SUM(i.quantity * i.unit_price * (1 - i.discount_pct / 100.0)), 2) AS net_revenue
FROM order_items AS i
JOIN products AS p ON p.product_id = i.product_id
WHERE i.quantity > 0
GROUP BY p.category;

Check the row count

The single best habit with joins: know how many rows you expect, and check. Joining order lines to products should return exactly one row per order line, because each line matches one product. Compare SELECT COUNT(*) FROM order_items with the count after the join. If the joined count is bigger, the “one” side had duplicates. If it is smaller, some lines had no match.

Syntax variations

• USING (product_id) is shorthand for ON a.product_id = b.product_id when the column names are identical. It works in Postgres, DuckDB, MySQL and BigQuery.
• Old-style comma joins, FROM order_items i, products p WHERE p.product_id = i.product_id, give the same result, but forgetting the WHERE condition creates a cross join with every combination of rows. Write explicit JOIN ... ON instead.
• NATURAL JOIN matches on every same-named column, which here would include unit_price and silently drop most rows. Avoid it.

Interview note

Be ready to explain INNER vs LEFT JOIN with a concrete example, and to say what happens to rows with no match. “An inner join drops them” is the core of it.