Task
Meta’s marketing analytics team wants a monthly cohort retention view for new user signups. Write a SQL query to measure how many users returned in each month after their signup month.
Requirements
- Define each user’s cohort as the month of their
signup_date from the facebook_users table.
- Treat a user as retained in a month if they have at least one event in
facebook_activity during that calendar month.
- Return one row per
signup_month and months_since_signup.
- Include:
signup_monthmonths_since_signup where signup month is 0, the next month is 1, etc.cohort_sizeretained_usersretention_rate rounded to 4 decimal places
- Only count activity on or after the user’s signup month. Ignore activity with a NULL
activity_date.
- Order results by
signup_month, then months_since_signup.
Table Definitions
facebook_users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_channel | VARCHAR(50) | Marketing acquisition source |
facebook_activity
| column | type | description |
|---|
| activity_id | INT | Unique activity identifier |
| user_id | INT | User who generated the activity |
| activity_date | DATE | Date of the activity |
| surface_name | VARCHAR(50) | Meta surface where activity happened |
Sample Data
facebook_users
| user_id | signup_date | acquisition_channel |
|---|
| 103 | 2024-02-20 | Organic |
| 101 | 2024-01-05 | Instagram Ads |
| 108 | 2024-03-28 | Referral |
| 105 | 2024-02-25 | Facebook Ads |
| 110 | 2024-04-15 | Organic |
| 102 | 2024-01-18 | Facebook Ads |
| 107 | 2024-03-03 | Instagram Ads |
| 104 | 2024-02-02 | Referral |
| 106 | 2024-03-10 | Organic |
| 109 | NULL | Facebook Ads |
facebook_activity
| activity_id | user_id | activity_date | surface_name |
|---|
| 8 | 104 | 2024-03-01 | Facebook |
| 2 | 101 | 2024-02-10 | Instagram |
| 11 | 106 | 2024-04-02 | Reels |
| 4 | 102 | 2024-03-05 | Facebook |
| 14 | 108 | 2024-04-10 | Messenger |
| 1 | 101 | 2024-01-20 | Facebook |
| 6 | 103 | 2024-02-22 | Instagram |
| 13 | 107 | 2024-05-01 | Facebook |
| 9 | 105 | 2024-02-28 | Facebook |
| 3 | 102 | 2024-01-25 | Messenger |
| 15 | 110 | 2024-04-20 | Instagram |
| 5 | 103 | 2024-04-01 | Facebook |
| 16 | 999 | 2024-03-15 | Facebook |
| 10 | 105 | NULL | Facebook |
| 12 | 107 | 2024-03-15 | Instagram |
| 7 | 104 | 2024-02-10 | Messenger |
Expected Output
| signup_month | months_since_signup | cohort_size | retained_users | retention_rate |
|---|
| 2024-01-01 | 0 | 2 | 2 | 1.0000 |
| 2024-01-01 | 1 | 2 | 1 | 0.5000 |
| 2024-01-01 | 2 | 2 | 1 | 0.5000 |
| 2024-02-01 | 0 | 3 | 3 | 1.0000 |
| 2024-02-01 | 1 | 3 | 1 | 0.3333 |
| 2024-02-01 | 2 | 3 | 1 | 0.3333 |
| 2024-03-01 | 0 | 3 | 1 | 0.3333 |
| 2024-03-01 | 1 | 3 | 2 | 0.6667 |
| 2024-03-01 | 2 | 3 | 1 | 0.3333 |
| 2024-04-01 | 0 | 1 | 1 | 1.0000 |
