'Revenue by product category for German customers' — one sentence, four tables: customers, orders, order_items, products. Real analytical questions almost never fit in two tables. This lesson teaches you to build the four-table chain — and to keep the row counts honest at every link.
What you'll learn
What
A multi-join query chains several JOIN clauses: each new join connects the result-so-far to one more table via its ON condition. Join types can mix (INNER here, LEFT there), and the order of ONs — not the order of tables in your head — determines what survives.
Why
Normalized schemas split facts across many tables on purpose; you'll formally see why in the Database Design module. The price is that answering questions means reassembling the pieces. Analysts who can't chain joins confidently either write wrong numbers (fan-out doubles their sums) or fall back to exporting tables and merging in pandas — slow and unscalable.
Where it's used
Virtually every production analytics query: star-schema rollups, funnel queries stitching users→sessions→events, and any report whose sentence contains two 'by's and a filter.
Where this runs in production
A merchant's 'sales by product' report joins orders → line items → products → collections, aggregating carefully so multi-item orders don't double-count.
Engagement analyses join profiles → sessions → playback events → titles to connect who watched what, when, on which device.
Margin dashboards join orders → order_items → products → suppliers to price every delivered basket against supplier cost.
Think of a relay race: the baton is your intermediate result. customers hands the baton to orders (now each row is a customer-order), orders hands it to order_items (now a customer-order-item), and order_items hands it to products (now enriched with product data). Each handoff can grow, shrink, or preserve the number of runners — and a fumbled handoff (bad ON clause) ruins every leg after it.
The grain is what one row represents. customers: one row = one customer. After joining orders: one row = one order (fan-out from one-to-many). After order_items: one row = one line item. Joining products (many-to-one) preserves that grain. Say the grain out loud after each link — every fan-out bug is a grain you lost track of.
SELECT c.country, p.category,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON oi.order_id = o.order_id
INNER JOIN products p ON p.product_id = oi.product_id
GROUP BY c.country, p.category;The canonical four-table rollup. Indent ONs vertically and align them — multi-join readability is a team sport. The final grain before GROUP BY is one row per line item, which is exactly the grain at which quantity × unit_price may be summed safely.
After fanning out to line items, each ORDER-level value (like o.shipping_fee) repeats once per item of that order — SUM(o.shipping_fee) now over-counts multi-item orders. Rule: aggregate each measure at its own grain. Either sum shipping fees in a separate query/CTE at order grain, or use SUM(DISTINCT …) tricks with extreme care (they break when two orders share a fee value... they don't — DISTINCT is per-value-set, use COUNT(DISTINCT o.order_id)-style patterns instead). The safe habit: one aggregate query per grain, joined afterwards.
You can LEFT-join one link and INNER-join the next — but an INNER join AFTER a LEFT join re-drops the NULL-filled rows the LEFT worked to keep (their key is NULL, matching nothing). If customers LEFT JOIN orders must keep order-less customers, every later join off the orders side must also be LEFT. The chain's weakest… strictest link wins.
Follow the baton: each join changes (or preserves) what one row means. Click each node — the grain annotation is the bug-prevention tool.
Show each order item with its customer name and product name — the minimal three-way stitch.
Read it as the relay: orders gains customer names (many-to-one), fans out to items (one-to-many), gains product names (many-to-one). Note oi appears in no SELECT column — bridge tables often exist purely to connect, contributing keys, not output.
name | order_id | product_name -------+----------+---------------- Anna | 101 | Wireless Mouse Anna | 101 | USB-C Hub Ben | 102 | Laptop Stand (3 rows)
Your task
Build the category report for one country. Tables: customers(customer_id, name, country), orders(order_id, customer_id), order_items(order_item_id, order_id, product_id, quantity, unit_price), products(product_id, product_name, category). Return category and revenue (SUM of quantity * unit_price) for customers in 'DE', sorted by revenue descending. Fill in the blanks.
category | revenue ----------+--------- audio | 120.00 cables | 4.50 (2 rows)
Write your solution in the editor on the right, then hit Run.
In FROM a JOIN b ON … JOIN c ON …, what may c's ON condition reference?
customers (1,000 rows) INNER JOIN orders (4,200 rows, every order has a valid customer) produces how many rows?
After the chain orders → order_items (line-item grain), which aggregate is INFLATED?
customers LEFT JOIN orders … INNER JOIN order_items … — what happens to customers with zero orders?
What does the dropping count reveal, and what is the right immediate fix?
List ALL customers with the number of distinct products they've ever bought (product_count) — customers with no purchases must appear with 0. Tables: customers(customer_id, name), orders(order_id, customer_id), order_items(order_item_id, order_id, product_id). Sort by product_count descending, then name ascending.
Walk me through writing a 'revenue by category per country' query over a customers/orders/order_items/products schema. What do you watch for?
I anchor on customers, join orders on customer_id, fan out to order_items on order_id, and enrich with products on product_id — INNER throughout, since entities without matches contribute no revenue. The thing I actively track is the grain after each link: customer → order → line item → line item. The measure I sum, quantity × unit_price, lives at line-item grain, which matches the pre-aggregation grain, so the totals are honest; if the report also needed an order-grain measure like shipping fees, I'd aggregate that separately at order grain and join the two aggregates rather than summing a repeated value. Before shipping the number I run one reconciliation: the query's grand total against a single-table SUM over order_items — if they differ, some link is dropping or duplicating rows and I count link by link to find it.
What is the LEFT-then-INNER trap in join chains, and how do you avoid it?
It's the pattern where a LEFT join preserves unmatched rows — say customers without orders — and a subsequent INNER join destroys that preservation: the preserved rows carry NULL join keys, the next ON condition evaluates to unknown for them, and INNER drops them. The query compiles, runs, and quietly excludes exactly the rows the LEFT was written to keep. The rule I apply: once any link is LEFT because preservation is a requirement, every later link that hangs off the nullable side must also be LEFT, and NULL-sensitive conditions on those tables must live in ON rather than WHERE for the same reason. In review, a LEFT followed by an INNER on the same chain branch is an automatic question: either the LEFT is unnecessary (make it INNER for clarity) or the INNER is a bug (make it LEFT). The mixed form is almost never intentional.
A multi-join query returns totals that don't match the source system. Describe your debugging process.
First I classify the direction: too high suggests fan-out (duplicated rows inflating SUMs), too low suggests dropped rows (INNER joins over referential holes, or an over-eager WHERE demoting a LEFT join). Then I bisect with link-by-link COUNT(*)s, adding one join at a time. Expectations per link type: one-to-many should grow the count to the child table's size; many-to-one should preserve it exactly. A many-to-one link that shrinks the count means missing reference rows — quantified precisely with an anti-join (LEFT JOIN … WHERE key IS NULL). A link that grows more than expected means duplicate keys on the 'one' side — checked with COUNT(*) vs COUNT(DISTINCT key). For inflation bugs I also re-derive the grain of every summed measure; an order-level column summed at item grain is the classic silent multiplier. This ritual localizes the defect to a single link in minutes, and the fix — LEFT instead of INNER, deduplication, or per-grain aggregation — follows directly from which check failed.
Common Mistakes to Avoid
1) Summing a coarse-grain measure at a fine grain — order-level shipping fees repeated per line item silently inflate totals; aggregate each measure at its own grain. 2) LEFT then INNER on the same branch — the INNER re-drops the rows the LEFT preserved; once LEFT, stay LEFT downstream. 3) WHERE conditions on nullable-side columns of a LEFT chain — they demote it to INNER; move them into ON. 4) Trusting a many-to-one join that shrinks the row count — that's referential holes discarding data, not normal behavior; anti-join to quantify. 5) t1/t2/t3 aliases in four-table queries — unreadable ON clauses hide reversed keys; alias by role (c, o, oi, p) and align the ONs.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: join chains with a relay-race analogy of your own.' • 'Quiz me: a four-table chain with row counts — I predict the count after each link.' • 'Show me the LEFT-then-INNER trap on a tiny dataset and both fixes.' • 'Explain why SUM(shipping_fee) inflates at line-item grain and the two-grain fix.' • 'Interview mode: give me a wrong multi-join total and have me debug it link by link.'
Glossary
Join chain — successive JOIN … ON clauses composing left to right. Intermediate result — the logical table existing after each link; the next ON may reference any of its columns. Grain — what one row represents; changes at one-to-many links, preserved at many-to-one links. Fan-out — row multiplication at a one-to-many link. Enrichment join — a many-to-one join adding columns without changing grain. Referential hole — a foreign-key value with no matching parent row; shrinks INNER many-to-one joins. Anti-join — LEFT JOIN … WHERE key IS NULL; isolates unmatched rows. Bridge (junction) table — a key-pair table implementing many-to-many; always traversed with two joins. LEFT-then-INNER trap — an INNER link re-dropping rows a prior LEFT preserved. Per-grain aggregation — summing each measure at its native grain before combining. Link-by-link counting — the debugging ritual of COUNT(*) after each join.
Recommended Resources
• Docs: PostgreSQL manual — 'Joined Tables' covers chain composition and mixed join types formally. • Read: Kimball's star-schema material (The Data Warehouse Toolkit, ch. 1–2) to see multi-join rollups as the intended usage pattern of dimensional models. • Practice: on any 3+ table schema, write a rollup, then deliberately break it (swap one LEFT for INNER, sum a coarse measure) and confirm you can detect both by counting and reconciling. • Next in DSM: chains answer 'combine and roll up' — Subqueries, opening the Advanced module, let one query ask questions ABOUT another's results.
Recap
✓ Joins chain left to right; each ON may reference every table already introduced. ✓ Track the grain after every link: one-to-many changes it (fan-out), many-to-one preserves it. ✓ Sum each measure at its native grain — coarse measures repeated at fine grain silently inflate totals; COUNT(DISTINCT key) counts coarse entities safely. ✓ Once a link is LEFT for preservation, every downstream link on that branch must be LEFT — an INNER re-drops the preserved rows. ✓ A many-to-one INNER join that shrinks the count reveals referential holes; quantify with an anti-join, patch with LEFT, prevent with constraints. ✓ Debug multi-joins by counting rows link by link — the failing link identifies itself. Next up: Subqueries. You can now assemble any flat question from many tables — the Advanced module begins with queries nested inside queries: filtering by another query's answer, comparing rows to aggregates, and the correlated form interviewers love.
Run your code to see the output here.