Compare User Retention by Cohort | Dataford Interview Questions

StreamWave wants to compare monthly retention across signup cohorts. Write a PostgreSQL query that calculates retention by cohort month over time.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort, count how many distinct users were active in month 0, month 1, and month 2 based on activity_date.
Return the cohort size, retained users, and retention rate for each cohort month and month offset.
Exclude activity that occurred before a user's signup date, and only include month offsets from 0 to 2.

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing acquisition source

user_activity

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

Your question is Compare User Retention by Cohort. 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.

StreamWave wants to compare monthly retention across signup cohorts. Write a PostgreSQL query that calculates retention by cohort month over time.

Define each user's cohort as the month of their signup_date.
For each cohort, count how many distinct users were active in month 0, month 1, and month 2 based on activity_date.
Return the cohort size, retained users, and retention rate for each cohort month and month offset.
Exclude activity that occurred before a user's signup date, and only include month offsets from 0 to 2.

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing acquisition source

user_activity

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

Your question is Compare User Retention by Cohort. 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.