SQL Quest › SQL Interview Questions › Subqueries & CTEs

Signup Cohort Activation Within 30 Days

MediumFreeQuerying BasicsSubqueries & CTEsJoinsAggregation & Grouping

A cohort funnel: of the people who signed up each month, how many did anything within their first 30 days? A user is activated when they have at least one completed transaction with ts no more than 30 days after signup_date (julianday(ts) - julianday(signup_date) <= 30).

Group users by signup month (strftime('%Y-%m', signup_date)). Return cohort, users, activated and activation_pct (100.0 * activated / users, rounded to 1 decimal). Every cohort appears, even one with zero activated users. Order by cohort 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

users

user_id	signup_date	country	home_currency	plan	plan_since	kyc_verified_at	referred_by	birth_year
1	2025-06-01	IE	EUR	standard	2025-06-01	2025-06-03 16:00:00	NULL	1972
2	2025-06-02	FR	EUR	standard	2025-06-02	2025-06-04 16:00:00	NULL	1979
3	2025-06-03	GB	GBP	standard	2025-06-03	2025-06-08 04:00:00	NULL	1977

transactions

txn_id	user_id	ts	type	amount	currency	amount_gbp	fee_gbp	merchant_category	merchant_country	counterparty_user_id	status	decline_reason
1	1	2025-06-14 11:01:51	fx_exchange	32271.15	JPY	167.81	1.68	NULL	NULL	NULL	completed	NULL
2	1	2025-06-14 11:17:45	bill_payment	59.53	EUR	50.6	0	utilities	IE	NULL	completed	NULL
3	7	2025-06-16 10:06:38	card_payment	122.27	EUR	103.93	0	shopping	ES	NULL	completed	NULL

Expected output: cohort=2025-06, users=16, activated=14, activation_pct=87.5 ...

Hint

Build the activated set once in a CTE (SELECT DISTINCT user_id ... WHERE status='completed' AND julianday(ts) - julianday(signup_date) <= 30), then LEFT JOIN it to users so cohorts with nobody activated still show. COUNT(a.user_id) counts only the matches.

Concepts

SELECT CTE LEFT JOIN GROUP BY julianday COUNT CTE + Cohort Analysis

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

In these company practice sets

Revolut

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Signup Cohort Activation Within 30 Days

Schema

Hint

Concepts

In these company practice sets

Related questions