Prevent join multiplication and reconcile money

Estimated study time: 75–105 minutes. Read, predict, run, explain, then attempt the independent work.

The manager wants to check sales against successful payments. It is tempting to join order_items directly to payments on order_id and sum both amounts. That query runs, the column names look sensible, and the result can still be wrong. The problem is the combination of two independent one-to-many relationships.

Order 1001 has two lines and two successful payments. Joining only on order_id pairs each line with each payment, producing four rows. The notebook line appears twice and the pen line appears twice. Summing line sales now gives 640 even though the order's line sales are 320. Payments are duplicated in the opposite direction. This is often called join fan-out; the useful mental model is simply “every matching pair becomes a row.”

Open the diagram at full size

Read the diagram explanation

Order 1001 has line amounts 240 and 80, payments 200 and 120. Joining on order identifier makes four pairs and doubles line sales from 320 to 640.

Fix the grain before combining measures

The intended reconciliation grain is one row per order. First aggregate lines to one row per order. Separately aggregate successful payments to one row per order. Then join these two summaries by order_id. Each input now has at most one row for the key, so the combined row contains compatible measures.

The worked repair uses a parenthesized SELECT in FROM. This is a derived table: a query result used as another query's input. Its alias, i or p, lets the outer query refer to its columns. Read and run each inner SELECT independently before considering the join. You already know their SELECT, WHERE and GROUP BY pieces; the new idea is putting their results behind names. Module 8 will introduce WITH as a more readable way to name a chain of intermediate results.

Do not repair this by adding SELECT DISTINCT to the final output. The wrong SUM has already been calculated. SUM(DISTINCT amount) is also unsafe: two real payments can have the same amount, so it may delete legitimate monetary contributions. Deduplicate events using a justified event identifier and source rule, not merely a matching monetary value.

Choose what reconciliation means

Successful payments total the collected amounts in this fixture. Failed attempts must not contribute. Refunds are recorded separately, so comparing successful payments to after-refund net sales would produce expected differences. Here we compare payments to line sales before refunds. Later, net sales subtracts refunds exactly once at order grain. Match the economic definitions before interpreting any difference as a defect.

The inner join in the reference reconciliation returns only orders present in both summaries. It is a useful arithmetic demonstration but is not, on its own, a coverage audit. An order with no successful payment disappears. In our fixture the 11 matched orders are the 11 completed orders; cancelled and pending orders are excluded. For real work, start from the required order population, left join both summaries, and flag missing summaries separately. Never label an absent payment feed as “zero difference.”

Reconciliation is strongest when it combines several independent checks: the expected order population; one output row per order; a hand calculation for a split-payment example; sum of lines versus successful payments; and a separate refund check. A zero grand-total difference can hide a positive error on one order offset by a negative error on another, so examine per-order differences before the final sum.

The deliberately wrong query below is safe to run on synthetic data because it only reads. It is included as a counterexample, not an approved metric. Keep it labelled if you reuse it in teaching.

Worked example 07 fanout

Predict four joined rows from two lines times two payments. The inflated 640 result is deliberately wrong; the true line total is 320.

-- Deliberately incorrect sales metric: two independent child tables multiply rows.
SELECT i.order_id,COUNT(*) AS joined_rows,
 SUM(i.quantity*i.unit_price-i.line_discount) AS inflated_sales
FROM aabha_sql.order_items AS i JOIN aabha_sql.payments AS p ON p.order_id=i.order_id
WHERE i.order_id=1001 AND p.status='succeeded'
GROUP BY i.order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/07_fanout.sql.

Worked example 07 reconcile

Each inner query has order grain. Successful payments and line sales agree for all eleven matched orders. Read the coverage limitation above before using this pattern in production.

SELECT i.order_id,i.line_sales,p.paid_amount,
 i.line_sales-p.paid_amount AS difference
FROM (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
) AS i
JOIN (
 SELECT order_id,SUM(amount) AS paid_amount
 FROM aabha_sql.payments WHERE status='succeeded' GROUP BY order_id
) AS p ON p.order_id=i.order_id
ORDER BY i.order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/07_reconcile.sql.

Independent practice

07a

Join items and successful payments for order 1006 directly. Return order_id, joined_rows and inflated_payments. Explain why the payment sum doubles.

Hint: Ignore the failed attempt; two lines still pair with two successful payments.

07b

Repair the order 1006 reconciliation using two grouped derived tables. Return order_id, line_sales and paid_amount.

Hint: Each derived table must output one row for order 1006 before the join.

Check before moving on

You can explain a four-row join on paper, identify each measure’s original grain, and justify why monetary DISTINCT is not a safe deduplication policy.