Disputed Spend by Risk Tier and Category

Disputed VALUE, not disputed COUNT — and the value is not where you would look for it. The chargebacks table has no amount column. The money at stake in a dispute is the amount of the transaction it points at, so the figure comes from transactions and the chargeback row only says which transactions count. LEFT JOIN on txn_id (one chargeback per transaction here, 76 over 76 distinct ids, so this join does not fan out), then a conditional SUM picks the disputed amounts out.

Segment by the two merchant attributes an issuer actually acts on — risk_tier and category — and put each cell next to its tier's overall rate so a reader can tell a bad category from a bad tier.

Return risk_tier, category, txn_count, total_spend, disputed_spend, disputed_pct (100.0 × disputed ÷ total, rounded to 2 decimals) and tier_disputed_pct (the same rate for the whole tier, rounded to 2 decimals). 15 rows. Order by risk_tier ascending, then disputed_pct descending, then category ascending.

The benchmark column is a window over the AGGREGATED rows — SUM(disputed_spend) OVER (PARTITION BY risk_tier) / SUM(total_spend) OVER (PARTITION BY risk_tier). Averaging the cell percentages instead would weight a 78-transaction category the same as a 240-transaction one.