Lesson 11 of 15 · 20 min
Row multiplication: the fan-out trap
By the end of this lesson you can explain why joining orders to both order_items and payments inflates totals, detect fan-out by counting rows per key, and rewrite the query so each child table is aggregated to one row per order before the join.
In Wadi Market most orders have exactly one payment row. But a card can be declined and retried (a failed row, then a captured row), and some customers split a payment across two methods (two captured rows). Hundreds of orders have two or three payment rows. That is correct data, and it is exactly what makes the next query wrong.
One child table: the parent repeats
You met this in the one-to-many lesson: join an order to its payments and the order appears once per payment. Any order-level value summed afterwards is counted once per payment. In the visual below, three orders with five payment rows become five joined rows, and a revenue total of 430 turns into 610.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons