Compute Month-over-Month Retention | Dataford Interview Questions

Write a SQL query to find the month-over-month retention rate of users across our digital properties.

Use the users and user_activity tables. A user is active in a month when they have at least one activity event for a property. Calculate retention separately for each property and month, excluding months without an active prior month.

Output

One row per property and month with an active prior month
Columns: property_name, activity_month, current_month_users, retained_users, prior_month_users, and retention_rate
retention_rate is retained users divided by prior-month users
Sort by property_name, then activity_month ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
created_at	DATE	Date the user account was created

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
property_name	VARCHAR(100)	Red Ventures digital property where activity occurred
activity_at	TIMESTAMP	Timestamp of the activity event

Your question is Compute Month-over-Month Retention. Start with the requirements and the two 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