Retention by Cohort SQL | Dataford Interview Questions

Your question is Retention by Cohort SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Yahoo Mail wants to measure how consistently users return after creating an account. Write a PostgreSQL query that calculates monthly retention for signup cohorts from January through March 2024.

Requirements

Define each user's cohort as the month of signup_date.
Treat a user as retained in a month when at least one Yahoo Mail activity event occurs during that calendar month.
Return periods 0 through 3 for every cohort, including periods with no retained users.
Report cohort size, retained users, and retention percentage. Round the percentage to one decimal place.
Deduplicate multiple events from the same user within a month and sort by cohort month and period.

Schema

users

Column	Type	Description
user_idPK	INT	Yahoo Mail user identifier
signup_date	DATE	Date when the account was created
account_status	VARCHAR(20)	Current account status

yahoo_mail_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the event
event_date	DATE	Date of the Yahoo Mail activity
event_type	VARCHAR(30)	Type of activity event