Unit 05.02: Diagnosing fan-out with a before-and-after count

Unit ID: SQL-M05-U03 - Estimated active time: 16-20 minutes Objective: recognise fan-out from a row count and quantify the damage it does to an aggregate.

The query that passes review

SELECT SUM(o.order_total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;
-- 9147789.00

It runs. It returns a plausible, well-formatted number. It is wrong by a factor of 3.4.

The true figure:

SELECT SUM(order_total) FROM orders;
-- 2701463.00

₹27.0L became ₹91.5L. Nothing errored.

Watching it happen on one order

Order 501 exists once in orders and three times in order_items:

SELECT o.order_id, o.order_total, i.sku
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_id = 501;
-- 501 | 1000.00 | SKU-A
-- 501 | 1000.00 | SKU-B
-- 501 | 1000.00 | SKU-C

order_total is repeated on every line. Summing that column now counts ₹1,000 three times:

SELECT SUM(o.order_total)
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_id = 501;
-- 3000.00

An order worth ₹1,000 contributed ₹3,000. Multiply that across 1,000 orders and you get the 3.4× inflation above - 3,400 item rows carrying 1,000 order totals.

The diagnosis takes ten seconds

SELECT COUNT(*) FROM orders;                                    -- 1000
SELECT COUNT(*) FROM orders o
JOIN order_items i ON i.order_id = o.order_id;                  -- 3400

The row count changed, so the grain changed. Once the grain has changed, any aggregate over a left-table column is inflated. That is the entire diagnostic.

Fan-out arrives from more than one direction

Joining to payments inflates the same figure differently:

SELECT SUM(o.order_total)
FROM orders o JOIN payments p ON p.order_id = o.order_id;
-- 3219960.00

Only the 197 instalment orders are duplicated, so the inflation is smaller and therefore harder to notice - ₹32.2L against a true ₹27.0L looks like a plausible variance rather than an obvious error. Small fan-out is more dangerous than large fan-out precisely because it survives a sanity check.

Practice

A colleague reports revenue up 19% month on month with no change in order volume. Their query joins orders to payments. Write the two queries you would run first.

-- 1. Did the grain change?
SELECT COUNT(*) FROM orders;                                              -- 1000
SELECT COUNT(*) FROM orders o JOIN payments p ON p.order_id = o.order_id; -- 1185

-- 2. How many orders have more than one payment?
SELECT COUNT(*) FROM (
  SELECT order_id FROM payments GROUP BY order_id HAVING COUNT(*) > 1
);
-- 197

Takeaway

Count before, count after. If the number changed and you did not intend it, stop - every aggregate you compute from here is inflated.

---

Mark complete