Netflix Business Context & Data Engineering Role Questions

WITH members_cohort AS (
  SELECT
    member_id,
    DATE_TRUNC('week', signup_date)::date AS cohort_week_start,
    signup_date::date AS signup_date
  FROM members
),
events AS (
  SELECT
    profile_id AS member_id,
    started_at::date AS event_date
  FROM views
),
cohort_size AS (
  SELECT
    cohort_week_start,
    COUNT(DISTINCT member_id) AS cohort_users
  FROM members_cohort
  GROUP BY cohort_week_start
),
activity AS (
  SELECT
    m.cohort_week_start,
    m.member_id,
    MIN(e.event_date) AS first_event_date -- first event after signup
  FROM members_cohort m
  LEFT JOIN events e
    ON e.member_id = m.member_id
    AND e.event_date >= m.signup_date -- only after signup
  GROUP BY m.cohort_week_start, m.member_id
),
retention AS (
  SELECT
    cohort_week_start,
    COUNT(DISTINCT CASE WHEN first_event_date IS NOT NULL AND first_event_date <= signup_date + 6 THEN member_id END) AS d7_active,
    COUNT(DISTINCT CASE WHEN first_event_date IS NOT NULL AND first_event_date <= signup_date + 29 THEN member_id END) AS d30_active
  FROM (
    SELECT a.*, m.signup_date
    FROM activity a
    JOIN members_cohort m USING (member_id, cohort_week_start)
  ) t
  GROUP BY cohort_week_start
)
SELECT
  r.cohort_week_start,
  c.cohort_users,
  r.d7_active,
  ROUND(100.0 * r.d7_active / c.cohort_users, 2) AS d7_retention_pct,
  r.d30_active,
  ROUND(100.0 * r.d30_active / c.cohort_users, 2) AS d30_retention_pct
FROM retention r
JOIN cohort_size c USING (cohort_week_start)
ORDER BY cohort_week_start;