SQL: Week-Over-Week Creator Retention | Dataford Interview Questions

Twitch Operations needs to measure whether creators who actively stream in Just Chatting or Music return to stream in the same category during the following week. Write a PostgreSQL query using the tables below.

Requirements

Treat a creator as active only when creators.status = 'active' and the stream has duration_minutes > 0.
Deduplicate creators to one record per category and calendar week, using weeks that begin on Monday.
For each category and week with activity in the following week, calculate the prior-week active creator count, retained creator count, and retention rate as a percentage.
Sort by category name and week start.

Schema

creators

Column	Type	Description
creator_idPK	INT	Unique Twitch creator identifier
display_name	VARCHAR(100)	Creator channel display name
status	VARCHAR(20)	Creator account status

categories

Column	Type	Description
category_idPK	INT	Unique Twitch category identifier
category_name	VARCHAR(100)	Twitch category name

streams

Column	Type	Description
stream_idPK	INT	Unique stream session identifier
creator_id	INT	Creator who started the stream
category_id	INT	Category selected for the stream
started_at	TIMESTAMP	Stream start timestamp
duration_minutes	INT	Stream duration in minutes

Your question is SQL: Week-Over-Week Creator Retention. 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