SQL for User Retention | Dataford Interview Questions

Your question is SQL for User Retention. Start with the requirements and the two tables on the right.

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

Problem

Write a SQL query to analyze user retention over time using a transactions or activity table.

Calculate retention by signup month and the number of months since signup. Include users with no qualifying activity when determining each cohort's size.

Output

One row per signup cohort and elapsed month with activity
Columns: cohort_month, months_since_signup, retained_users, cohort_users, and retention_rate
Include activity on or after signup, order by cohort_month and months_since_signup ascending, and report retention as a decimal rounded to four places

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user registered

activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
activity_date	DATE	Date of user activity