SQL Quest › SQL Interview Questions › Aggregation & Grouping
Chargeback Reason Codes: Resolved and Still Open
A dispute that has not been resolved has no resolved_at. 17 of the 76 chargebacks in this book carry NULL there. That is the whole lesson: COUNT(*) counts rows, COUNT(resolved_at) counts rows where that column is NOT NULL, and the difference between them is the open work. Same table, same GROUP BY, two different numbers.
Return reason_code, chargebacks (COUNT(*)), resolved (COUNT(resolved_at)) and unresolved (the difference). Order by chargebacks descending, then reason_code ascending — two reason codes are tied at 13, so that second key decides their order.
Do not reach for status instead. In this ledger the NULL resolved_at rows do not line up with status = 'open': only 3 of the 19 open disputes are missing a resolution timestamp. Answer the question the columns actually answer.
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
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: fraud_card_present 22 16 6; fraud_card_not_present 19 15 4; ...
Hint
SUM(CASE WHEN resolved_at IS NULL THEN 1 ELSE 0 END) is the same number written the long way.Concepts
SELECT GROUP BY Aggregation IS NULL ORDER BY NULL Handling
Practise the topic: SQL practice questions · GROUP BY exercises · NULL handling practice
Read the concept: How to find duplicates in SQL
In these company practice sets
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.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test