Monthly Retention via Paid Channels | Dataford Interview Questions

Write a SQL query to calculate the monthly retention rate of users acquired through paid marketing channels at Wise. Energy. Treat paid_search, paid_social, and paid_display as paid channels. Retention is measured when a paid user records activity in a later calendar month.

Output

One row per acquisition month and later activity month, using cohort_month and retention_month.
Return cohort_users, retained_users, and retention_rate_pct, calculated against the original paid acquisition cohort.
Include months with zero retained users, exclude the acquisition month, and order by cohort_month, then retention_month.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquired_at	DATE	Date when the user was acquired
acquisition_channel	VARCHAR(50)	Marketing channel responsible for acquisition

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity identifier
user_id	INT	User who performed the activity
activity_at	DATE	Date when the activity occurred
activity_type	VARCHAR(50)	Type of user activity

Your question is Monthly Retention via Paid Channels. 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