SQL Quest › SQL Interview Questions › Joins
Signup-Month Cohort Spend
Do older cardholders spend more? Group accounts into cohorts by the month they signed up (strftime('%Y-%m', signup_at) on the accounts table) and total their card spend from transactions. The join is one-to-many — one account, many transactions — so count accounts with COUNT(DISTINCT a.account_id), or every cohort's size is inflated by its transaction count.
Return signup_month (YYYY-MM), accounts (distinct accounts in the cohort), total_spend (SUM of amount, rounded to 2 decimals), and spend_per_account (total_spend ÷ accounts, rounded to 2 decimals). Order by signup_month 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: 2024-11 12 20610.34 1717.53 ...
Hint
Concepts
SELECT JOIN GROUP BY COUNT DISTINCT strftime JOIN + Date Functions
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