Paid Search Retention SQL | Dataford Interview Questions

Write a SQL query to calculate the month-over-month retention rate of users acquired through paid search at Gusto. Treat a user as retained when they have at least one product activity event in the calendar month immediately after their acquisition month. Use the provided tables and return one row per acquisition month, including months with no retained users.

Output

Columns: acquisition_month, cohort_users, retained_users, and retention_rate
One row per paid-search acquisition month
retention_rate is retained users divided by cohort users
Order by acquisition_month ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique Gusto user identifier
signup_date	DATE	Date the user signed up

marketing_attributions

Column	Type	Description
attribution_idPK	INT	Unique attribution record identifier
user_id	INT	Attributed user identifier
source	VARCHAR(50)	Marketing acquisition source

product_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the activity
activity_date	DATE	Date of product activity
activity_type	VARCHAR(50)	Type of product activity

Your question is Paid Search 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