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". Free accounts get 10 free challenge solves, and this can be one of them.
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
Read the concept: LEFT JOIN vs INNER JOIN
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