Monthly Retention by Acquisition Source | Dataford Interview Questions

Write a SQL query to find the monthly retention rate of subscribers who purchased a Peloton Bike versus those who joined via the app.

Use the subscribers and subscriber_activity tables. Treat a subscriber as retained in a month when they have at least one activity record during that calendar month after joining. Include the acquisition cohort and compare the two acquisition groups.

Output

One row per acquisition segment, cohort month, and activity month with observed activity.
Columns: subscriber_segment, cohort_month, activity_month, cohort_subscribers, retained_subscribers, and retention_rate.
Sort by segment, cohort month, and activity month ascending.

Schema

subscribers

Column	Type	Description
subscriber_idPK	INT	Unique subscriber identifier
joined_at	DATE	Date the subscriber joined Peloton
acquisition_source	VARCHAR(30)	Subscriber acquisition path, such as Bike Purchase or App

subscriber_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
subscriber_id	INT	Subscriber associated with the activity
activity_date	DATE	Date of subscriber activity

Your question is Monthly Retention by Acquisition Source. 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