SQL Quest › SQL Interview Questions › SQL traps › Join fan-out

SQL join fan-out: why SUM and COUNT inflate after a JOIN

Joining two child tables through their parent (instead of on the key that links them) repeats each row once per match, so every SUM and COUNT after the join is inflated.

The task

Report card spend and the number of chargebacks for each merchant risk tier. chargebacks carries both merchant_id and txn_id, and the analyst joins it on merchant_id. The data is SQL Quest's synthetic card-transactions set (accounts, merchants, transactions, chargebacks), and every result on this page is what the query returns on it.

The query that looks right

SELECT m.risk_tier,
       ROUND(SUM(t.amount), 2) AS spend,
       COUNT(cb.chargeback_id) AS chargebacks
FROM merchants m
JOIN transactions t  ON t.merchant_id = m.merchant_id
JOIN chargebacks cb  ON cb.merchant_id = m.merchant_id
GROUP BY m.risk_tier
ORDER BY m.risk_tier;

High-risk merchants appear to have 243,172.54 of spend and 878 chargebacks.

Why

The fix

Fix 1 — Join on the key that makes the rows one-to-one — the transaction

SELECT m.risk_tier,
       ROUND(SUM(t.amount), 2) AS spend,
       COUNT(cb.chargeback_id) AS chargebacks
FROM merchants m
JOIN transactions t      ON t.merchant_id = m.merchant_id
LEFT JOIN chargebacks cb ON cb.txn_id = t.txn_id
GROUP BY m.risk_tier
ORDER BY m.risk_tier;

Fix 2 — Or aggregate each child table first, then join the totals

WITH spend AS (
  SELECT merchant_id, SUM(amount) AS spend
  FROM transactions GROUP BY merchant_id
), disputes AS (
  SELECT merchant_id, COUNT(*) AS chargebacks
  FROM chargebacks GROUP BY merchant_id
)
SELECT m.risk_tier,
       ROUND(SUM(s.spend), 2) AS spend,
       COALESCE(SUM(d.chargebacks), 0) AS chargebacks
FROM merchants m
JOIN spend s          ON s.merchant_id = m.merchant_id
LEFT JOIN disputes d  ON d.merchant_id = m.merchant_id
GROUP BY m.risk_tier
ORDER BY m.risk_tier;

Right answer: High-risk merchants have 78,986.43 of spend across 262 transactions, and 10 chargebacks — the wrong query overstated spend about threefold.

Rule of thumb. Before trusting a SUM or COUNT after a join, check that the join did not change the row count of the table you are summing.

Read next: SQL joins explained, with the row counts each join produces

Where this trap is tested

This trap is question 2 of our Capital One CodeSignal-style mock, and again in question 1 of our Capital One live SQL round mock — a timed practice screen on the same kind of data. The mock asks it in a different form, so this page does not give its answer away.

Practise it

Related SQL traps