Your question is SQL Weekly Retention by Channel. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
acquisition_channel, cohort_week, retention_week, cohort_users, retained_users, and retention_rate.retention_rate is a percentage rounded to two decimals.| 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 |
| 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 |