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".
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
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
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