Measure Retention After Product Launch | Dataford Interview Questions

NovaPlay launched a major product update on 2024-04-01 and wants to understand whether user retention changed after the launch. Write a PostgreSQL query that uses a cohort table to compare retention for pre-launch and post-launch signup cohorts.

Requirements

Treat each user's cohort as the month of their signup_date from the cohort_table.
For each cohort month, calculate month 1 retention: the percentage of users who had at least one activity event in the calendar month immediately after their cohort month.
Label each cohort as pre_launch or post_launch based on whether the cohort month is before 2024-04-01.
Return one row per cohort month with cohort size, retained users, and retention rate rounded to 2 decimals.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

cohort_table

Column	Type	Description
user_idPK	INT	User identifier
cohort_month	DATE	First day of the user's signup month

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User identifier tied to the activity
activity_date	DATE	Date of the activity event
activity_type	VARCHAR(50)	Type of activity event

Your question is Measure Retention After Product Launch. 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