Window Functions for Retention | Dataford Interview Questions

Ancestry wants to understand whether users who join in January 2024 continue using the Family Tree experience. Write a PostgreSQL query to calculate monthly retention for this signup cohort from January through March 2024.

Requirements

Define the cohort as users whose signup_date falls in January 2024.
Count each cohort user at most once per calendar month, even if they generate multiple Family Tree events.
Use a window function to deduplicate monthly activity, then return active users and retention percentage for months 0, 1, and 2 relative to signup cohort month.
Include months with zero activity and order results chronologically.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Ancestry user identifier
signup_date	DATE	Date the user created an Ancestry account

product_events

Column	Type	Description
event_idPK	INT	Unique product event identifier
user_id	INT	User associated with the event
event_at	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Ancestry product surface generating the event

Your question is Window Functions for Retention. 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