Month-over-Month Creator Retention | Dataford Interview Questions

Given a database of user video interactions, write a SQL query to calculate the month-over-month retention rate of active creators.

Use the creators and video_interactions tables. A creator is active in a month when they have at least one recorded interaction during that month.

Output

One row per month after the first observed month, with columns month_start, previous_month_active_creators, retained_creators, and retention_rate.
Include creators active in both the current month and immediately preceding month in retained_creators.
Order by month_start ascending. Return retention_rate as a percentage rounded to two decimal places.

Schema

creators

Column	Type	Description
creator_idPK	INT	Unique creator identifier
creator_name	VARCHAR(100)	Creator display name

video_interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
creator_id	INT	Creator associated with the interaction
interaction_at	TIMESTAMP	Timestamp when the interaction occurred

Your question is Month-over-Month Creator 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