Retention Cohorts With Complex Joins | Dataford Interview Questions

Twitch wants to measure how consistently viewers return after their first qualifying stream session. Write a PostgreSQL query that builds monthly viewer retention cohorts for activity during 2025.

Requirements

Define each viewer's cohort as the calendar month of their first session on an active Twitch channel during 2025.
Calculate retention for cohort months 0 through 11, where month 0 is the cohort month.
Return one row per cohort with the cohort size and the distinct number of retained viewers in each month.
Include only sessions associated with active channels, deduplicate multiple sessions by viewer and calendar month, and sort cohorts chronologically.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Twitch viewer identifier
display_name	VARCHAR(80)	Twitch display name
created_at	DATE	Date the viewer account was created

stream_sessions

Column	Type	Description
session_idPK	INT	Unique watched-session identifier
user_id	INT	Viewer who watched the session
channel_id	INT	Twitch channel associated with the session
started_at	TIMESTAMP	Session start timestamp

channels

Column	Type	Description
channel_idPK	INT	Unique Twitch channel identifier
channel_name	VARCHAR(100)	Twitch channel name
is_active	BOOLEAN	Whether the channel is eligible for retention analysis

Your question is Retention Cohorts With Complex Joins. Start with the requirements and the three 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