Build a cohort retention matrix in SQL: % of each monthly signup cohort still active N months later.

TL;DR: Join a cohort-assignment CTE (signup month per user) to a distinct user-month activity CTE, compute months-since-signup, and divide each cell by the month-0 cohort size, not by the prior month. The line that earns the hire is the right-censoring caveat: recent cohorts have empty future cells, not zero retention.

How to approach it

Define the three ingredients out loud: a cohort assignment (each user's signup month), an activity fact (one row per user per active month), and a distance dimension (months between the two). The output is cohort month × months-since-signup → retention %. Clarify "active" (any event? a qualifying action?) and whether retention is month-N (active in exactly that month) or cumulative (active in N or later), interviewers usually want month-N.

A strong answer

WITH cohorts AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(event_ts)) AS cohort_month
  FROM events
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
         user_id,
         DATE_TRUNC('month', event_ts) AS active_month
  FROM events
),
joined AS (
  SELECT
    c.cohort_month,
    DATEDIFF('month', c.cohort_month, a.active_month) AS month_n,
    a.user_id
  FROM cohorts c
  JOIN activity a USING (user_id)
)
SELECT
  cohort_month,
  month_n,
  COUNT(DISTINCT user_id) AS active_users,
  ROUND(100.0 * COUNT(DISTINCT user_id)
        / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
            PARTITION BY cohort_month ORDER BY month_n), 1) AS retention_pct
FROM joined
WHERE month_n >= 0
GROUP BY cohort_month, month_n
ORDER BY cohort_month, month_n;

Points to narrate:

• DISTINCT in the activity CTE sets the grain. Without it, heavy users inflate counts, though COUNT(DISTINCT user_id) here saves you, saying why both layers exist shows you think in grains.
• Month 0 is the cohort size (everyone is active in their signup month by construction), which is why the FIRST_VALUE window over the aggregate gives the denominator. If the engine dislikes windows over aggregates, join a cohort_sizes CTE instead, offer that fallback.
• Right-censoring: a March cohort can't have month-6 retention in June. Either filter to mature cells (cohort_month + month_n <= current month) or the matrix's bottom-right triangle will read as 0% and someone will panic in a meeting. Saying this unprompted is the strongest signal in the question.

The wrong-denominator mistake deserves numbers, because the two versions sound interchangeable and lead to opposite decisions. A January cohort of 100 users has 50 active in month 1 and 40 in month 2. Month-2 retention is 40/100 = 40%: of everyone who signed up, forty percent are still here. Month-2 survival is 40/50 = 80%: of those who made it through month 1, eighty percent stayed. Both are legitimate metrics; the catastrophe is the label swap. A PM told "80% retention at month 2" concludes the product is sticky and spends on acquisition; the truth, that three of five users are gone within two months, points the same budget at onboarding instead. When a stakeholder asks for "retention," confirming which of these two they mean is a thirty-second question that has saved real quarters of misdirected spend, and mentioning that in the interview is what the grain talk is for.

For presentation as an actual matrix (months as columns), add conditional aggregation: SUM(CASE WHEN month_n = 1 THEN ... END) AS m1, etc., which neatly bridges into the pivoting question.

What interviewers probe next

• "Why is month-3 retention higher than month-2 for one cohort?", month-N is non-monotonic (users return); cumulative-style "retained through N" is monotonic. Know the difference.
• "Weekly cohorts at 1B events/day?", pre-aggregate user-month activity incrementally (a daily MERGE into a user-month table) instead of scanning raw events each run.
• "Define 'active' better", qualifying-event filters; how the metric changes.
• "Unbounded vs N-day retention windows" for app-style retention (D1/D7/D30).

Common mistakes

• Computing the denominator from month_n = 1 instead of cohort size.
• Forgetting WHERE month_n >= 0 when dirty data has pre-signup events.
• No censoring caveat, produces the "retention cliff" artifact.
• Joining events-to-events without pre-aggregation at scale.