SQL · hard · Asked 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.

How to answer it

Define it before you write it: retention for cohort month M in month M+1 is the share of users active in M who are also active in M+1. Say that sentence; half the marks are for the definition.

WITH monthly AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_ts)::date AS month
  FROM events
),
cohorts AS (
  SELECT user_id, MIN(month) AS cohort_month
  FROM monthly
  GROUP BY user_id
),
cohort_size AS (
  SELECT cohort_month, COUNT(*) AS users
  FROM cohorts
  GROUP BY cohort_month
)
SELECT
  c.cohort_month,
  m.month,
  COUNT(DISTINCT m.user_id) AS active_users,
  COUNT(DISTINCT m.user_id)::float / s.users AS retention
FROM cohorts c
JOIN monthly m ON m.user_id = c.user_id
JOIN cohort_size s ON s.cohort_month = c.cohort_month
GROUP BY c.cohort_month, m.month, s.users
ORDER BY c.cohort_month, m.month;

The DISTINCT in monthly matters: one user with 400 events in a month is one active user. Cohort size comes from its own CTE, not from the joined rows, so the denominator is the number of users who started in that month regardless of how many stayed. (COUNT(DISTINCT ...) is not allowed as a window function in Postgres, which is why it is a CTE and not an OVER clause.)

If the interviewer means "active in M and again in M+1" rather than cohorts, it is a self-join on user_id where m2.month = m1.month + INTERVAL '1 month'; ask which one they want.

• What they are checking: whether you define retention before computing it, and whether you count distinct users.
• Common mistake: dividing by the number of rows in the joined result, which is not the cohort size.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Find users who logged in 3 or more consecutive days.

Practice this for real

• Check your resume against a Data Scientist job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card