SQL Quest › SQL Interview Questions › Joins

Chargeback Rate Per Merchant

MediumFreeQuerying BasicsJoinsAggregation & Grouping

Which merchants generate disputes? A chargeback rate is chargebacks ÷ transactions per merchant. Join merchants to transactions (INNER — a merchant with no transactions has no rate), then LEFT JOIN chargebacks on txn_id so merchants with zero disputes still count their transactions. Watch the grain: count chargebacks with COUNT(c.chargeback_id), which ignores the NULLs the LEFT JOIN produces; COUNT(*) would count every transaction as a dispute.

Return merchant_id, name, category, txn_count, chargeback_count, and chargeback_rate_pct (100.0 × chargebacks ÷ transactions, rounded to 2 decimals). Keep only merchants with at least 3 chargebacks (HAVING). Order by chargeback_rate_pct descending, then merchant_id ascending.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect". Free accounts get 10 free challenge solves, and this can be one of them.

Schema

merchants

merchant_id	name	category	country	risk_tier
1	BigBox Mart	Grocery	TR	high
2	Quick Stop	Electronics	JP	high
3	Aurora Cafe	Travel	TR	high

transactions

txn_id	account_id	amount	txn_at	merchant_id	lat	lng	status
1	149	151.84	2026-03-04T12:39:39.078Z	24	40.342	-74.4092	completed
2	21	10.72	2026-03-04T13:04:50.641Z	7	52.9366	13.3229	completed
3	35	158.78	2026-03-04T13:27:15.528Z	25	52.7397	13.3134	completed

chargebacks

chargeback_id	txn_id	account_id	merchant_id	reason_code	opened_at	resolved_at	status	related_chargeback_id
1	8	160	10	service_not_received	2026-03-06T16:52:17.429Z	2026-04-06T16:52:17.429Z	split	NULL
2	164	42	16	fraud_card_not_present	2026-03-16T23:50:46.792Z	2026-04-22T23:50:46.792Z	open	1
3	172	48	20	fraud_card_present	2026-03-12T05:22:57.195Z	2026-04-14T05:22:57.195Z	cardholder_won	2

Expected output: PillPath 96 txns, 8 chargebacks, 8.33% ...

Hint

FROM merchants m JOIN transactions t ON t.merchant_id = m.merchant_id LEFT JOIN chargebacks c ON c.txn_id = t.txn_id GROUP BY m.merchant_id, m.name, m.category HAVING COUNT(c.chargeback_id) >= 3.

Concepts

SELECT JOIN LEFT JOIN GROUP BY HAVING ROUND GROUP BY + HAVING

Practise the topic: SQL practice questions · JOIN practice · GROUP BY exercises

Read the concept: SQL JOINs explained

The trap to watch for: Average of averages

In these company practice sets

Capital One · Stripe

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Preparing for Capital One? How the CodeSignal data analyst assessment works.

Chargeback Rate Per Merchant

Schema

Hint

Concepts

In these company practice sets

Related questions