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".
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
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