The Query That Doubled the Revenue
An analyst is asked whether APAC outsold EMEA last quarter. She runs:
SELECT o.region, SUM(o.total) AS revenue FROM orders o JOIN line_items li ON li.order_id = o.id GROUP BY o.region;
The orders table holds 5 orders: EMEA 200 and 300; APAC 150, 250, 400. The line_items table holds 4, 1, 2, 5 and 3 items respectively for orders 101-105. The query returns EMEA 1,100 and APAC 2,750, and she reports that APAC revenue is roughly 2.5 times EMEA's. A reviewer recomputes from the orders table alone and gets EMEA 500 and APAC 800. The analyst defends her query by noting that GROUP BY and SUM "always produce correct totals."
1. Show why the two answer sets differ, and give the correct revenue figures and ratio.
Read the worked answer
The join fans out: each order row is repeated once per matching line item, and SUM(o.total) then adds the same order total once per item.
- EMEA: 200 appears 4 times + 300 appears 1 time = 800 + 300 = 1,100
- APAC: 150x2 + 250x5 + 400x3 = 300 + 1,250 + 1,200 = 2,750
Correct, one row per order: EMEA 500, APAC 800, total 1,300. APAC is 1.6x EMEA, not 2.5x.
The important point is that the error is not a constant factor - it scales with items per order. APAC's orders average more line items, so APAC is inflated far more than EMEA. A reviewer who assumed "both numbers are inflated equally" would still get the wrong ranking. GROUP BY and SUM are correct at the grain of the measure; the measure here is the order, and the FROM clause changed the grain to line item.
2. The analyst then computes "average order value" as SUM(o.total)/COUNT(*) from the same query. What number does she get, what is the true AOV, and why does the method fail even when the answers look close?
Read the worked answer
Her expression evaluates SUM(total) = 3,850 divided by COUNT(*) = 15 joined rows, giving 256.67.
The true AOV is 1,300 / 5 = 260.00.
The two are close here by coincidence, which is the most dangerous outcome - a plausible-looking number that validates the method. The computation is not an average order value: it is an order-value-per-line-item weighted by item count (dollars per line item). It goes wrong badly whenever item count correlates with order size - large orders with many items get the order total counted many times while contributing only a proportional share of the denominator, which biases the result in a direction that depends on the correlation.
The correct pattern is to aggregate at the measure's grain first (SELECT region, SUM(total), COUNT(*) FROM orders GROUP BY region) or to deduplicate before joining (SUM over SELECT DISTINCT id, region, total FROM orders), and never to divide an aggregate over a fanned-out join by COUNT(*) of that join.
3. Give a general check that would have caught this before the number left the analyst's desk, and state one other way the same query can go wrong even after a fix.
Read the worked answer
Fan-out check: for each group, compare COUNT(*) with COUNT(DISTINCT o.id) in the same query. If they differ, rows were duplicated by a one-to-many join and every SUM/AVG over the parent table is suspect. Here EMEA would show COUNT(*)=5 versus COUNT(DISTINCT o.id)=2 - immediately visible. A second guard is to reconcile against a source-of-truth total (the orders table alone, or the financial system) before publishing.
Other failure mode: the join type changes the population. A LEFT JOIN keeps orders with no line items (correct for revenue, but they get NULL item rows, and any COUNT(li.id) silently becomes 0 while COUNT(*) counts them), while an INNER JOIN silently drops them - an order with no items disappears from the revenue total entirely. Always check whether the join preserves the parent row count you intended, and whether zero-item orders should or should not be in the answer.