Daily Retention by Genre SQL | Dataford Interview Questions

Write a query to calculate the daily retention rate of users who watched a specific genre of content.

For this exercise, use Drama as the specific genre and define retention as watching any content on the following calendar day. Include every day with at least one qualifying genre viewer.

Output

One row per cohort date, with watched_date, cohort_users, retained_users, and daily_retention_rate.
Include each qualifying viewer once per date, calculate the rate as retained users divided by cohort users, and return the rate rounded to four decimal places.
Order by watched_date ascending.

Schema

content

Column	Type	Description
content_idPK	INT	Unique content identifier
title	VARCHAR(150)	Content title
genre	VARCHAR(50)	Primary content genre

viewing_events

Column	Type	Description
event_idPK	INT	Unique viewing event identifier
user_id	VARCHAR(40)	Viewer identifier
content_id	INT	Viewed content identifier
watched_at	TIMESTAMP	Timestamp when viewing occurred

Your question is Daily Retention by Genre SQL. 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