SQL · hard · Reported at Meta

Compute month-over-month user retention from an events table.

Asked in Data Scientist interviews, in the SQL round.

Short answer

Self-join cohort month vs activity month, count distinct users, divide by cohort size.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

events

user_idevent_date
12026-01-05
22026-01-09
32026-01-15
42026-01-20
12026-02-03
22026-02-11
52026-02-14
12026-03-02
52026-03-08
62026-03-12
22026-03-19

How to answer it

Definition

Of the users first seen in a given month, what share came back in each later month. The output is a cohort grid: cohort month down the side, months since signup across the top.

Approach

Example

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_ts)) AS cohort_month
  FROM   events
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT e.user_id,
         c.cohort_month,
         DATE_TRUNC('month', e.event_ts) AS active_month
  FROM   events e
  JOIN   cohort c ON c.user_id = e.user_id
),
sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM   cohort GROUP BY cohort_month
)
SELECT a.cohort_month,
       (EXTRACT(YEAR FROM a.active_month) - EXTRACT(YEAR FROM a.cohort_month)) * 12
         + (EXTRACT(MONTH FROM a.active_month) - EXTRACT(MONTH FROM a.cohort_month)) AS month_number,
       COUNT(DISTINCT a.user_id) AS retained,
       ROUND(100.0 * COUNT(DISTINCT a.user_id) / MAX(s.cohort_size), 1) AS pct
FROM   activity a
JOIN   sizes s ON s.cohort_month = a.cohort_month
GROUP BY a.cohort_month, month_number
ORDER BY a.cohort_month, month_number;

Output

cohort_month | month_number | retained |  pct
-------------+--------------+----------+-------
2025-01-01   |            0 |     1200 | 100.0
2025-01-01   |            1 |      516 |  43.0
2025-01-01   |            2 |      372 |  31.0

Key points

FAQ

The 30-second answer

I would build it as a cohort grid. One CTE giving each user their cohort month, the month of their first event. A second with the distinct months each user was active. Join those, compute the month offset between the two, and count distinct users per cohort and offset, divided by the cohort size.
The details I would flag are that it has to be COUNT DISTINCT, since a user with forty events is still one user, and that the denominator is the original cohort size rather than last month's survivors, or it becomes a different metric. I would also point out that month zero is a hundred percent by construction, so if it is not, the cohort logic is wrong.

In the interview

Practice this for real