Meta Signup Month Retention Cohorts | Dataford Interview Questions

Your question is Meta Signup Month Retention Cohorts. 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.

Problem

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_month
- months_since_signup where signup month is 0, the next month is 1, etc.
- cohort_size
- retained_users
- retention_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.

Schema

facebook_users

Column	Type	Description
user_idPK	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_idPK	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