Monthly Retention SQL Query | Dataford Interview Questions

Write a SQL query to find the monthly retention rate of customers acquired through organic search.

Use customer activity after acquisition and report cohorts through June 2024. Treat a customer as retained in a month if they have at least one activity event during that month.

Output

One row per organic-search acquisition cohort and month since acquisition.
Columns: cohort_month, months_since_acquisition, cohort_size, and retention_rate.
Include zero-retention months through June 2024, ordered by cohort_month and months_since_acquisition.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
acquired_at	TIMESTAMP	Customer acquisition timestamp
acquisition_channel	VARCHAR(50)	Marketing channel credited with acquisition

customer_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
customer_id	INT	Customer associated with the activity
activity_at	TIMESTAMP	Timestamp of customer activity
activity_type	VARCHAR(50)	Type of customer activity

Your question is Monthly Retention SQL Query. 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