SQL Retention for Subscribers | Dataford Interview Questions

Write a SQL query to calculate the retention rate of subscribers over a 30-day period.

Assume a subscriber is retained when they have activity exactly 30 days after their subscription date. Include only subscribers whose 30th day is on or before the latest recorded activity date.

Output

Return one row with eligible_subscribers, retained_subscribers, and retention_rate_percent.
Calculate the rate as retained subscribers divided by eligible subscribers, expressed as a percentage rounded to two decimal places.

Schema

subscribers

Column	Type	Description
subscriber_idPK	INT	Unique subscriber identifier
subscription_date	DATE	Date the subscriber started their subscription

subscriber_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
subscriber_id	INT	Subscriber associated with the activity
event_date	DATE	Date of subscriber activity
event_type	VARCHAR(30)	Type of subscriber activity

Your question is SQL Retention for Subscribers. 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