SQLIntermediate
Why is this SUM double the real number?
“A revenue query joins orders to order_items and sums orders.total, and the number is far too high. Explain.”
What this tests
- Understanding join cardinality
- Where aggregation belongs relative to a join
Answers by level
Read the beginner answer first and notice what is missing.
The join produces one row per order line, so an order with three items appears three times and sum(orders.total) counts it three times. It is a correctness bug, not a performance one, and nothing flags it.
Fix by not joining when the aggregate only needs orders, or by aggregating the child side in a subquery before joining. sum(DISTINCT total) is wrong because two different orders can share a total.
Green flags · Red flags
Strong green flag · Calls it a correctness bug that monitoring would never catch.
Green flags
- Identifies the multiplication immediately
- Rejects sum(DISTINCT) as a fix
- Aggregates the child before joining
Red flags
- Reaches for DISTINCT without understanding why
- Thinks it is a rounding or currency issue
Follow-up questions
F1
When is sum(DISTINCT x) actually correct?
Scenario
Finance reports €10M, the dashboard reports €38M. Both query the same orders. Find the difference.