Business Problem

Netflix's subscriber analytics team is preparing the investor letter and needs the classic cohort retention triangle: for every signup cohort (grouped by the month a subscriber joined), what percentage is still active 1, 2, 3+ months later? Leadership wants to compare how the January, February, and March 2024 cohorts are retaining against each other to see whether a recent onboarding redesign actually improved stickiness, and they need month-number-relative retention (month 0, month 1, month 2...) rather than raw calendar dates, since cohorts started at different points. Subscription businesses live or die by retention, and a single 'overall churn rate' number hides what's really happening. Cohort retention analysis groups subscribers by signup month and tracks what fraction of each cohort is still active N months after joining, which is the standard way SaaS and streaming companies like Netflix evaluate whether product or pricing changes are improving long-term stickiness rather than just short-term signups.

Dataset & Schema

Subscribers with subscriber_id, signup_date, plan_type, country; subscription_events recording one 'active_month' row per subscriber per calendar month they were active/billed, used to derive which months each subscriber was retained.

CREATE TABLE subscribers (
  subscriber_id INTEGER PRIMARY KEY,
  signup_date DATE,
  plan_type TEXT,
  country TEXT
);

CREATE TABLE subscription_events (
  event_id INTEGER PRIMARY KEY,
  subscriber_id INTEGER,
  event_type TEXT, -- 'active_month' recorded once per calendar month the subscriber was billed/active
  event_month DATE, -- first day of the month
  FOREIGN KEY (subscriber_id) REFERENCES subscribers(subscriber_id)
);

SQL Solution

WITH cohorts AS (
  SELECT
    subscriber_id,
    DATE_TRUNC('month', signup_date)::date AS cohort_month
  FROM subscribers
),
activity AS (
  SELECT
    a.subscriber_id,
    c.cohort_month,
    a.event_month,
    (DATE_PART('year', a.event_month) - DATE_PART('year', c.cohort_month)) * 12
      + (DATE_PART('month', a.event_month) - DATE_PART('month', c.cohort_month)) AS month_number
  FROM subscription_events a
  JOIN cohorts c ON c.subscriber_id = a.subscriber_id
  WHERE a.event_type = 'active_month'
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY cohort_month
),
retention AS (
  SELECT
    cohort_month,
    month_number,
    COUNT(DISTINCT subscriber_id) AS retained_subscribers
  FROM activity
  GROUP BY cohort_month, month_number
)
SELECT
  TO_CHAR(r.cohort_month, 'YYYY-MM') AS cohort_month,
  cs.cohort_size,
  r.month_number,
  r.retained_subscribers,
  ROUND(100.0 * r.retained_subscribers / cs.cohort_size, 1) AS retention_rate_pct
FROM retention r
JOIN cohort_sizes cs ON cs.cohort_month = r.cohort_month
ORDER BY r.cohort_month, r.month_number;

Explanation