Monthly Retention Cohorts | Dataford Interview Questions

Bain & Company uses an internal analytics platform to monitor how consistently users return after their signup month. Write a PostgreSQL query that produces monthly retention cohorts from signup-month activity.

Assume a user belongs to a cohort based on their signup month. Month 0 is the signup month, and Months 1 through 3 are the following three calendar months. Count distinct users with activity in each period.

Requirements

Self-join user_activity so one alias identifies signup-month activity and the second alias identifies subsequent activity.
Use conditional aggregation to return distinct retained-user counts for Months 0, 1, 2, and 3.
Exclude users without a signup date, group by signup month, and order chronologically.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user signed up

user_activity

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

Your question is Monthly Retention Cohorts. 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