What is join fan-out and how does it cause duplicate rows or inflated aggregates?

How to think about it

This is one of the most common silent correctness bugs in analytics SQL. The query runs, returns a confident number, and nobody questions it — until a BI report is off by 3×. The interviewer wants to see you catch the grain mismatch up front, by asking the one question that prevents it: is the join key unique on both sides?

How fan-out happens

Picture orders with one row per order, joined to order_items with many rows per order:

SELECT o.order_id, o.customer, oi.product, o.order_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

If an order has 3 line items, that order’s order_total now appears in 3 result rows. Any SUM(order_total) over this result counts it three times.

A worked example — the inflated aggregate

Aarav’s order is $300 with 3 items; Bea’s is $150 with 1; Chen’s is $200 with 2. Summing order_total after the join multiplies each total by its item count:

-- WRONG: SUM(o.order_total) is inflated by the number of line items per order
SELECT customer, SUM(o.order_total) AS inflated_spend
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY customer;

customer	inflated_spend
Aarav	900
Bea	150
Chen	400

Aarav’s $300 became $900 (×3 items) and Chen’s $200 became $400 (×2). Bea, with a single item, is the only correct figure — which is exactly why the bug hides: the one-item orders look fine.

The fix — aggregate the many-side first

Collapse order_items to one row per order before joining, so the grain matches and order_total is counted once:

WITH item_totals AS (
  SELECT order_id, SUM(line_amount) AS items_total
  FROM order_items
  GROUP BY order_id            -- now one row per order
)
SELECT o.customer, SUM(o.order_total) AS total_spend
FROM orders o
JOIN item_totals it ON o.order_id = it.order_id
GROUP BY o.customer;

customer	total_spend
Aarav	300
Bea	150
Chen	200

Now every total is right. And to detect fan-out in a query you didn’t write, compare the row count to the distinct key count:

SELECT COUNT(*) AS rows, COUNT(DISTINCT o.order_id) AS distinct_orders
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

rows	distinct_orders
6	3

6 ≠ 3 is the smell — the join doubled (here tripled-and-doubled) the order grain.

Many-to-many makes it worse

When both sides are non-unique on the key, the result is a Cartesian product of the matching subsets — 5 left rows × 4 right rows = 20 rows for that one key. That’s multiplicative, not additive, and can quietly turn a 10-million-row table into a multi-billion-row result.