SQL Retention by Channel | Dataford Interview Questions

Write a SQL query to calculate the month-over-month retention rate of users acquired through different marketing channels.

Use the users and user_activity tables. A retained user is an acquired user with at least one activity event during the calendar month immediately following acquisition.

Output

One row per non-null acquisition channel and acquisition month with at least one acquired user.
Columns: acquisition_channel, acquisition_month, acquired_users, retained_users, and retention_rate.
Return retention_rate as a percentage rounded to two decimal places, ordered by acquisition month and channel.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquired_at	TIMESTAMP	Timestamp when the user was acquired
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
activity_at	TIMESTAMP	Timestamp of the user activity event

Your question is SQL Retention by Channel. 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