Why is AVG(o.amount) over a fanned-out join wrong in a way COUNT(DISTINCT) cannot fix?
answer
- the average is over joined rows, not orders
- rows with more children count more often
- both parts of the fraction are inflated
- the weights differ per parent
- it becomes a child-count-weighted average
basics
~20 sFan-out turns the average into one weighted by each parent's child count: orders with many items count many times. Both numerator and denominator are inflated by row-specific factors, so no DISTINCT wrapper repairs it — only removing the multiplication does.
solid answer
~50 s`AVG` is `SUM` divided by the count of non-NULL values, both taken over the rows the join produced. After a one-to-many join each order contributes once *per child row*, so an order with 10 items has ten times the influence of an order with one. The result is a child-count-weighted average, and because the weights differ per row there is no constant to divide out. `COUNT(DISTINCT o.order_id)` repairs the denominator but leaves the numerator inflated; `AVG(DISTINCT o.amount)` averages the distinct price points, collapsing two different orders that cost the same. The only correct fix is to eliminate the fan-out before averaging: compute the average over `orders` alone, or over a derived table holding one row per order. The production danger is drift — as child rows accumulate over time, the weights shift and a query nobody touched reports a slowly moving number.
code
sql · 7 lines-- orders: 100 (3 items), 100 (1 item), 400 (1 item)
SELECT AVG(o.amount) AS avg_order -- 800 / 5 = 160
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;
SELECT AVG(amount) AS avg_order -- 600 / 3 = 200 (the truth)
FROM orders;go deeper
Know that AVG is SUM divided by the number of values, and that after a one-to-many join those values are the repeated parent rows — so the average is taken over the wrong set of rows.
Derive the weighted-average formula and show with a three-row example why AVG(DISTINCT) and a COUNT(DISTINCT) denominator both give new wrong answers rather than the right one.
Focus on production judgment: this metric drifts as child counts grow, so treat any ratio built over a multi-table join as suspect and reconcile it against the same ratio computed from the parent table alone.
Own the definitional problem: average-style metrics need a declared grain and a single blessed implementation, or independent teams will publish different values for the same named number and both will be defensible.
## The arithmetic `AVG(x)` is defined as the sum of the non-NULL `x` values divided by how many of them there were, evaluated over the rows the aggregate receives. After a join to a one-to-many child, those rows are parent rows repeated once per match. So for parents with amounts `a_k` and child counts `n_k`: ``` AVG over joined rows = (Σ n_k · a_k) / (Σ n_k) ``` That is not the average of the `a_k`; it is a **weighted** average with weights `n_k`. Concretely, with orders of 100 (3 items), 100 (1 item) and 400 (1 item): ```sql SELECT AVG(o.amount) FROM orders o JOIN order_items i ON i.order_id = o.order_id; -- (300 + 100 + 400) / 5 = 160 SELECT AVG(amount) FROM orders; -- 600 / 3 = 200 ``` 160 versus 200 — and the direction of the error depends entirely on whether cheap or expensive orders happen to carry more items. It can bias high or low, which makes it harder to spot than a `SUM` that is uniformly too big. ## Why the usual repairs fail **`COUNT(DISTINCT o.order_id)` as the denominator.** Writing `SUM(o.amount) / COUNT(DISTINCT o.order_id)` fixes half the fraction: the denominator becomes 3, but the numerator is still 800, giving 266.67. Repairing one side of a ratio and not the other produces a number that is wrong in a new way. **`AVG(DISTINCT o.amount)`.** This averages the distinct *values*: `{100, 400}` → 250. It collapses the two genuinely different orders that both cost 100, so it is wrong even on a single table with no join at all. **`SUM(DISTINCT o.amount) / COUNT(DISTINCT o.order_id)`.** 500 / 3 = 166.67. Same value-versus-row confusion in the numerator. **Dividing by a fan-out factor.** There is no single factor. Each parent has its own `n_k`, and parents with zero children contribute nothing to divide. The pattern behind all four failures: fan-out corrupts the *multiset of rows*, and every one of these attempts operates on values or on one side of the arithmetic. Only restoring the row multiset restores the answer. ## The correct rewrites If the child is only a filter, do not join it — test existence, which does not duplicate: ```sql SELECT AVG(o.amount) FROM orders o WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.order_id); ``` If you need a measure from the child too, collapse the child to one row per order first, then average over the one-row-per-order result: ```sql WITH per_order AS ( SELECT o.order_id, o.amount, COUNT(i.item_id) AS item_count FROM orders o LEFT JOIN order_items i ON i.order_id = o.order_id GROUP BY o.order_id, o.amount ) SELECT AVG(amount) AS avg_order_value, AVG(item_count) AS avg_items_per_order FROM per_order; ``` The inner query is the fan-out, contained: grouping back to `order_id` restores one row per order, and `amount` is functionally dependent on the grouped key so it can be carried through (list it in the `GROUP BY` for portability). Everything the outer query averages is now at order grain. ## Why this bites in production A fanned-out `SUM` is usually caught because the number is visibly too large; a fanned-out `AVG` sits in a plausible range. Worse, the weights are not static. If the average number of items per order grows over quarters, or if a new product line produces orders with many small line items, the weighting shifts and the reported average order value drifts — with no code change to blame. Two teams computing "average order value" from the same warehouse then disagree, one having joined a child table for a filter and the other not. The defensive habits are the same as for `SUM`, applied earlier: know the grain of every table in the `FROM` clause, aggregate children to the parent grain before any ratio is computed, and reconcile a new average against the same average computed from the parent table alone. Any ratio whose numerator and denominator are drawn from different grains deserves the same suspicion — the general rule is that a ratio is only meaningful when both parts are counted over the same set of things. ## Interview framing Write the weighted-average formula, give a three-order example with the two numbers, and say explicitly that no `DISTINCT` variant fixes it because `DISTINCT` deduplicates values while fan-out duplicates rows. Then close on drift: this is the metric bug that ages, and that is the reason it is worth designing out rather than checking for.
- Does SUM(o.amount) / COUNT(DISTINCT o.order_id) fix it?No. It repairs only the denominator: the numerator still adds each order's amount once per child row. With orders of 100 (3 items), 100 and 400, it gives 800 / 3 = 266.67 rather than 200. Repairing one side of a ratio replaces one wrong number with a different wrong number.
- Why is a fanned-out AVG harder to notice than a fanned-out SUM?An inflated SUM is visibly too large and often fails a control total. A weighted AVG stays inside a plausible range and can be biased high or low depending on whether cheap or expensive parents carry more child rows. It also drifts over time as child counts grow, so there is no single moment where the number obviously broke.
- How do you write the correct average when you also need a child-derived measure?Contain the fan-out in an inner query that groups back to the parent key, producing one row per order with both the amount and the child count, then average over that result. The outer query then operates entirely at order grain, so both averages are unweighted and consistent with each other.
saying these in an interview costs you the question
- Says the average is fine because SUM and COUNT inflate equally
- Fixes only the denominator with COUNT(DISTINCT)
- Suggests AVG(DISTINCT amount) as the repair
- Assumes one division by a fan-out factor corrects it
- Treats a plausible-looking average as validated