SQL Quest › SQL Interview Questions › Joins
Dormant Cards — No Transactions in the Last 14 Days
Retention teams call these dormant cards. The ledger's last day is 2026-05-03; treat "the last 14 days" as transactions on or after 2026-04-19. Find every account with NO transaction in that window — an anti-join. Put the recent transactions in a CTE, LEFT JOIN accounts to it, and keep the rows where the join found nothing (IS NULL). NOT EXISTS is the equivalent subquery form.
Return account_id, email, country, and last_txn_at (the account's most recent transaction timestamp in the whole ledger, any date). Order by last_txn_at ascending, then account_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
accounts
| account_id | 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 |
Expected output: 11 accounts with no transaction on or after 2026-04-19
Hint
SELECT, LEFT JOIN, IS NULL, and open the hint there if you stall.Concepts
SELECT LEFT JOIN IS NULL CTE Date Functions Anti-Join + CTE
Practise the topic: SQL practice questions · JOIN practice · NULL handling practice · CTE practice · Date function practice
Read the concept: The anti-join, three ways
The trap to watch for: BETWEEN on timestamps · LEFT JOIN with a WHERE filter
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