Paid Search MoM Retention SQL | Dataford Interview Questions

Write a SQL query to calculate the month-over-month retention rate of users acquired through paid search campaigns at Retool.

Use the provided campaign, user, and activity data. Include monthly transitions where users were active in the preceding month, including transitions with zero retained users.

Output

One row per consecutive month transition
Columns: previous_month, retention_month, previous_active_users, retained_users, and retention_rate
Include only users acquired through paid search and activity on or after acquisition
Order by previous_month ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique Retool user identifier
acquired_at	DATE	Date the user was acquired
campaign_id	INT	Campaign attributed to the user's acquisition

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Acquisition channel

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity
event_at	TIMESTAMP	Timestamp of the activity event
event_type	VARCHAR(50)	Type of product activity

Your question is Paid Search MoM Retention SQL. Start with the requirements and the three 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