Level 4 · Lines per order without double counting

When you join a table to another where each row matches many rows, the first table’s rows are repeated once per match. This is called fan-out, and it is the source of most inflated numbers in analytics.

Grain

Every table has a grain: what one row represents. orders is one row per order. order_items is one row per order line. After

FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id

the result is at the order-line grain. An order with three lines appears three times, and every column from orders, including shipping_fee, is copied onto all three rows.

What breaks

• COUNT(*) now counts lines, not orders. The starter query reports roughly two to three times the real number of orders.
• SUM(o.shipping_fee) adds each order’s fee once per line. A $6.95 fee on a three-line order is counted as $20.85. Finance will notice before you do.
• AVG(o.shipping_fee) is weighted toward orders with many lines.

Nothing errors. The query runs, the numbers look plausible, and they are wrong.

Fixes

Count distinct keys for counts on the “one” side:

COUNT(DISTINCT o.order_id) AS orders,
COUNT(*) AS order_lines

Aggregate before joining for sums on the “one” side. Summarize lines per order first, so the join is one-to-one again:

SELECT o.channel,
       SUM(o.shipping_fee) AS shipping,
       SUM(l.lines) AS order_lines
FROM orders AS o
JOIN (
  SELECT order_id, COUNT(*) AS lines
  FROM order_items
  GROUP BY order_id
) AS l ON l.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.channel;

You will do this cleanly with CTEs in chapter 7. Do not use SUM(DISTINCT o.shipping_fee): two different orders with the same $6.95 fee would be collapsed into one.

Ratios after a join

lines_per_order is order lines divided by orders. Both are integers, so multiply by 1.0 before dividing. In Postgres, COUNT(*) / COUNT(DISTINCT o.order_id) would truncate 2.6 to 2. DuckDB and MySQL return a decimal, BigQuery’s / always returns a float, but * 1.0 keeps the query portable.

How to catch fan-out

Before aggregating, run the join with COUNT(*) and compare it to the row count of the table whose grain you want. If you expected one row per order and got more, you have fan-out. Senior analysts do this check by reflex every time they add a join.

Interview note

“Our revenue dashboard doubled after someone added a join to the promotions table. What happened?” is a common scenario question. Name fan-out, explain grain, and offer both fixes: COUNT(DISTINCT ...) for counts and pre-aggregation for sums.