SQL Quest › SQL Interview Questions › SQL traps › Average of averages

SQL average of averages: why AVG of group rates is wrong

The average of group averages (or of group rates) is not the overall average: every group gets one vote however many rows it has.

The task

Report the overall chargeback rate — chargebacks per transaction. A dashboard already shows the rate for each account status (active, flagged), and the analyst averages the two rates. 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 ROUND(100 * AVG(rate), 2) AS chargeback_rate_pct
FROM (
  SELECT a.status,
         1.0 * COUNT(cb.chargeback_id) / COUNT(*) AS rate
  FROM transactions t
  JOIN accounts a         ON a.account_id = t.account_id
  LEFT JOIN chargebacks cb ON cb.txn_id = t.txn_id
  GROUP BY a.status
);

Returns: 13.10

A 13.10% chargeback rate.

The two groups being averaged

SELECT a.status,
       COUNT(*) AS transactions,
       COUNT(cb.chargeback_id) AS chargebacks,
       ROUND(100.0 * COUNT(cb.chargeback_id) / COUNT(*), 2) AS rate_pct
FROM transactions t
JOIN accounts a          ON a.account_id = t.account_id
LEFT JOIN chargebacks cb ON cb.txn_id = t.txn_id
GROUP BY a.status
ORDER BY a.status;

Why

The fix

Fix 1 — One division over all the rows

SELECT ROUND(100.0 * COUNT(cb.chargeback_id) / COUNT(*), 2) AS chargeback_rate_pct
FROM transactions t
LEFT JOIN chargebacks cb ON cb.txn_id = t.txn_id;

Returns: 3.51

Fix 2 — Or keep the group table, and weight it: sum the parts, then divide

SELECT ROUND(100.0 * SUM(chargebacks) / SUM(transactions), 2) AS chargeback_rate_pct
FROM (
  SELECT a.status,
         COUNT(*) AS transactions,
         COUNT(cb.chargeback_id) AS chargebacks
  FROM transactions t
  JOIN accounts a          ON a.account_id = t.account_id
  LEFT JOIN chargebacks cb ON cb.txn_id = t.txn_id
  GROUP BY a.status
);

Returns: 3.51

Right answer: The overall chargeback rate is 3.51%.

Rule of thumb. For an overall average or rate, divide totals: SUM(numerator) / SUM(denominator), or one AVG over the rows. Average the group results only when you want each group weighted equally — and say so.

Where this trap is tested

This trap is question 11 of our Capital One CodeSignal-style 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