Spend and Disputes Per Cardholder — Without the Fan-Out

This is the mistake a card screen is built to catch. An account has many transactions AND many chargebacks. Join all three tables in one chain — accounts JOIN transactions JOIN chargebacks ON account_id — and every transaction row is repeated once per chargeback the account has. Account 156 has 11 transactions and 5 chargebacks; the naive query returns 55 rows for it and reports its spend at five times the true figure. Nothing errors. The number is just wrong.

The fix is to never join two one-to-many children of the same parent at row level: aggregate each branch to one row per account first, in its own CTE, then join the summaries.

Return the 20 cardholders with the most disputes: account_id, email, txn_count, total_spend (rounded to 2 decimals) and chargeback_count. Every account has transactions but most have no chargebacks, so that branch is a LEFT JOIN and its NULL becomes 0 — COALESCE(d.chargeback_count, 0). Order by chargeback_count descending, then total_spend descending, then account_id ascending.