Retention Rate for a Series | Dataford Interview Questions

Prime Video wants to measure whether viewers return to watch the same series after their initial viewing. Write a PostgreSQL query to calculate the 30-day retention rate for users who first watched series 101, The Peripheral, during January 2025.

Requirements

Define the cohort as distinct users whose first qualifying view of series 101 occurred from 2025-01-01 through 2025-01-31.
Count a user as retained if they watched series 101 again more than 0 and no more than 30 days after their first qualifying view.
Return the cohort size, retained-user count, and retention rate as a percentage rounded to two decimal places.
Prevent multiple follow-up views from counting a user more than once.

Schema

users

Column	Type	Description
user_idPK	INT	Prime Video user identifier
region	VARCHAR(30)	User region

series

Column	Type	Description
series_idPK	INT	Series identifier
title	VARCHAR(150)	Series title
genre	VARCHAR(50)	Series genre

watch_events

Column	Type	Description
event_idPK	INT	Viewing event identifier
user_id	INT	Viewer identifier referencing users
series_id	INT	Watched series identifier referencing series
watched_at	TIMESTAMP	Time of the viewing event
watch_seconds	INT	Number of seconds watched

Your question is Retention Rate for a Series. 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