SQL Quest › SQL Interview Questions › Subqueries & CTEs

Cardholders Who Have Never Disputed a Charge

MediumFreeQuerying BasicsSubqueries & CTEsJoinsAggregation & Grouping

The other side of the chargeback question: who has never raised one? 147 of the 200 accounts in this book have no dispute against them, and "has none" is not a filter you can write with a JOIN and a WHERE — a join finds rows that exist. NOT EXISTS is the direct form: a correlated subquery that stops as soon as it finds one matching chargeback, and the row survives only when it finds none.

Return the 20 highest-spending such cardholders: account_id, email, country, txn_count (their transaction count) and total_spend (SUM of amount, rounded to 2 decimals). Order by total_spend descending, then account_id ascending.

LEFT JOIN chargebacks … WHERE c.chargeback_id IS NULL is the same answer written as an anti-join — it is worth writing both and seeing that they agree. What is NOT the same answer is WHERE c.account_id <> a.account_id, which is the mistake this pattern exists to prevent.

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

accounts

account_id	email	signup_at	country	device_fingerprint	ip_block	status
1	user1@example.com	2026-02-24T00:00:00.000Z	TR	dev_13c0cdad	41.116.196.153	active
2	user2@inbox.dev	2025-05-13T00:00:00.000Z	JP	dev_26576d49	38.98.201.102	active
3	user3@inbox.dev	2026-04-25T00:00:00.000Z	JP	dev_42f1a115	190.77.45.244	flagged

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: 150 user150@mail.test TR 12 10126.18; 140 user140@mail.test US 11 8867.9; ...

Hint

FROM accounts a JOIN transactions t ON t.account_id = a.account_id WHERE NOT EXISTS (SELECT 1 FROM chargebacks c WHERE c.account_id = a.account_id) GROUP BY a.account_id, a.email, a.country. The subquery's SELECT list is irrelevant — SELECT 1 is the convention because nothing reads it.

Concepts

SELECT Subquery JOIN GROUP BY SUM

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

Read the concept: Recursive CTE explained

The trap to watch for: NOT IN with NULL

In these company practice sets

Capital One · Revolut

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.

Cardholders Who Have Never Disputed a Charge

Schema

Hint

Concepts

In these company practice sets

Related questions