SQL Quest › SQL Interview Questions › Joins
Card Spend by Country
Country is a property of the cardholder, not of the transaction — it lives on accounts, so join before you group. Then the question every screen asks next: how many PEOPLE, and how many SWIPES? Those are two different counts. COUNT(*) counts rows in the joined result, which after a one-to-many join is transactions. COUNT(DISTINCT a.account_id) counts cardholders.
Return country, cardholders (distinct accounts), txn_count (all transactions), total_spend (SUM of amount, rounded to 2 decimals) and txns_per_cardholder (txn_count ÷ cardholders, rounded to 2 decimals). Order by total_spend descending, then country ascending. Write the ratio as 1.0 * COUNT(*) / COUNT(DISTINCT ...) — two integers divide as integers in SQLite and you would get 10, not 10.44.
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
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 |
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 |
Expected output: TR 45 470 83040.13 10.44; US 47 504 79679.17 10.72; ...
Hint
SELECT, JOIN, GROUP BY, and open the hint there if you stall.Concepts
SELECT JOIN GROUP BY COUNT DISTINCT Aggregation JOIN + GROUP BY
Practise the topic: SQL practice questions · JOIN practice · GROUP BY exercises
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