SQL Retention Cohorts with Windows | Dataford Interview Questions

Write a query using SQL window functions to calculate running 30-day active user retention cohorts across different subscription tiers.

Use the provided users, subscriptions, and activity_events tables. Treat each user's signup month as the cohort, use the non-ended subscription at signup, and evaluate activity during signup day through day 30.

Output

One row per subscription tier, signup cohort month, and checkpoint day 0, 7, 14, 21, 28, or 30.
Return tier, cohort_month, days_since_signup, cohort_size, running_active_users, and retention_rate_pct.
Include cohorts with no qualifying activity, order by tier, cohort_month, and days_since_signup.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	User signup date

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
user_id	INT	Subscribed user
tier	VARCHAR(30)	Subscription tier
started_at	DATE	Subscription start date
ended_at	DATE	Subscription end date, or NULL when active

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User generating the activity
activity_at	TIMESTAMP	Timestamp of user activity
event_type	VARCHAR(40)	Activity event type

Your question is SQL Retention Cohorts with Windows. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem