How do you check that a join did not multiply rows before trusting a SUM over it?

## Why you need a check at all Join fan-out produces no error, no warning and no obviously wrong value. A revenue figure that is 2.7× too high is still a number with a currency sign on it. The distortion factor also varies per parent row — orders with many items are inflated more than orders with one — so the result is not even uniformly wrong in a way someone might notice as "about double". The only reliable defence is to check the *row multiplicity* of the join explicitly, before any aggregate hides it. ## Probe 1: is the join key unique on the other side? The question "will this join fan out?" is entirely answered by whether the join key is unique in the table being joined: ```sql SELECT order_id, COUNT(*) AS rows_per_order FROM order_items GROUP BY order_id HAVING COUNT(*) > 1 ORDER BY rows_per_order DESC; ``` If this returns nothing, the child is at most one row per order and joining it cannot duplicate anything. If it returns rows, the largest `rows_per_order` is the worst-case multiplier you are about to apply to every parent measure. The schema can answer this too — a `PRIMARY KEY` or `UNIQUE` constraint on the join key is a *guarantee*, not an observation. Distinguish the two: data that happens to be unique today can stop being unique tomorrow, while a constraint cannot. A query whose correctness rests on an unenforced assumption is a bug waiting for a row. ## Probe 2: did the join multiply the driving table? After the join is written, count rows before aggregating: ```sql SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT o.order_id) AS distinct_orders FROM orders o JOIN order_items i ON i.order_id = o.order_id; ``` Interpretation is mechanical: - `joined_rows = distinct_orders` — one row per order survived; additive aggregates over `orders` columns are safe. - `joined_rows > distinct_orders` — fan-out; every `SUM`, `COUNT` and `AVG` over `orders` columns in that query is wrong. - `distinct_orders < (SELECT COUNT(*) FROM orders)` — the inner join also *dropped* orders with no items, a separate problem that a `LEFT JOIN` would fix. That third line matters: fan-out and row loss often coexist, and a total can be simultaneously inflated by duplication and deflated by dropped parents. ## Probe 3: the control total The most convincing check compares against a number computed without the join: ```sql SELECT SUM(amount) FROM orders; -- the truth ``` Whatever the reporting query says total revenue is, it must reconcile with this (allowing only for rows the query deliberately filters out). Control totals catch fan-out, accidental cross joins and dropped rows in one comparison, which is why they belong in the review of any query that feeds a metric. ## Habits that make the checks unnecessary - **Know the grain of every table in the `FROM` clause.** Write it in a comment: `-- one row per order`, `-- one row per shipment`. A join between two different grains is exactly where fan-out lives. - **Aggregate the child to the parent's grain before joining** whenever the child is many-per-parent, so the question never arises. - **Be suspicious of any number that moved when a table was added to the query** without the filter changing. Adding a join should not change a total that comes from a different table. - **Watch for a `SELECT DISTINCT` or a stray `DISTINCT` inside an aggregate** in inherited code. Both are usually scar tissue from a fan-out someone patched rather than fixed, and both can mask a genuine multiplicity problem while quietly deleting real duplicate detail rows. ## Interview framing Give the two probes concretely — the `HAVING COUNT(*) > 1` uniqueness test on the child and the `COUNT(*)` versus `COUNT(DISTINCT pk)` comparison on the join — and then say that you prefer to design the fan-out away by aggregating each child to the parent's grain first. Mentioning the control total shows you have actually had to defend a number to someone who cared about it.