SQL Weekly Retention by Channel | Dataford Interview Questions

Write a SQL query to calculate weekly retention for users acquired from each marketing channel.

Use the supplied user and activity records. Exclude users whose acquisition channel is unknown, include cohorts through the latest valid activity week, and represent weeks with no returning users as zero retention.

Output

One row per acquisition channel, cohort week, and retention week.
Columns: acquisition_channel, cohort_week, retention_week, cohort_users, retained_users, and retention_rate.
Sort by channel, cohort week, and retention week ascending. retention_rate is a percentage rounded to two decimals.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel credited with acquiring the user
acquisition_date	DATE	Date the user was acquired

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 when the user was active

Your question is SQL Weekly 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