ORIGINAL TECHNICAL PRACTICE
Prevent a join from inflating totals
An order table has one row per order and an item table has several rows per order. Why can summing order totals after joining them produce the wrong result?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
The join repeats each order row once per matching item. Summing a measure stored at order grain after that fan-out counts the same total repeatedly.
Work through it
- Declare the grain and uniqueness key of every input. Determine whether the requested measure belongs to an order, item, customer or another entity.
- Aggregate item-level measures to order grain before joining when that matches the task. Alternatively, compute order-level and item-level metrics separately and combine at a shared grain.
- Check joined row counts and reconcile totals against the original order table. Include orders with no items and duplicate or invalid keys in the test data.
Tradeoffs to discuss
- Pre-aggregation can reduce join volume, but it may discard detail needed for later filters. Apply filters at the grain where their meaning is defined.
Common pitfalls
- SUM(DISTINCT order_total) is not a general fix: two different orders can legitimately have the same total. DISTINCT on all output columns may leave the measure duplicated.
SYNTHETIC WORKED EXAMPLE
Trace the repeated total.
Imagine two orders: A totals 100 and has two items; B also totals 100 and has one item. The correct order total is 200.
- Joining the order header to its items produces three rows: A/100, A/100 and B/100. Summing the repeated order total gives 300.
SUM(DISTINCT order_total)gives 100, which also fails: two different orders legitimately share the same total.- For an order-level report, aggregate the item data by
order_idbefore joining. The resulting one-row-per-order relation can retain A’s two-item count and B’s one-item count while summing the order total once per order.
Verify the key is unique at that grain and reconcile the resulting 200 against the original order headers. Choose the aggregation and filters to match the question; a correct total alone does not validate every measure.
Inspect the MIS reconciliation demoThe recorded demo is free to inspect. The complete Python/SQL toolkit is a paid category resource.