Cohort Analysis SQL | Dataford Interview Questions

Initio Capital wants to measure how consistently users return to its product after signup. Write a PostgreSQL query that calculates monthly retention cohorts for users who signed up during the three calendar months from January through March 2025.

Requirements

Assign each eligible user to a monthly signup cohort.
Count the total users in each cohort.
Count distinct active users by cohort month and activity month, excluding events before signup and outside the analysis period.
Return retention as a percentage, ordered by cohort month and activity month.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
signed_up_at	TIMESTAMP	Timestamp when the user signed up

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
occurred_at	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(50)	Type of product activity

Your question is Cohort Analysis SQL. 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