SQL Quest › SQL Interview Questions › Joins

Signup-Month Cohort Spend

EasyFreeQuerying BasicsJoinsAggregation & Grouping

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". Free accounts get 10 free challenge solves, and this can be one of them.

Schema

accounts

account_id	email	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

FROM accounts a JOIN transactions t ON t.account_id = a.account_id GROUP BY strftime('%Y-%m', a.signup_at). The per-account figure is SUM(t.amount) / COUNT(DISTINCT a.account_id).

Concepts

SELECT JOIN GROUP BY COUNT DISTINCT strftime JOIN + Date Functions

Practise the topic: SQL practice questions · JOIN practice · GROUP BY exercises

Read the concept: SQL JOINs explained

The trap to watch for: Join fan-out

In these company practice sets

Capital One · Revolut

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.

Signup-Month Cohort Spend

Schema

Hint

Concepts

In these company practice sets

Related questions