Calculate Weekly Cohort Retention

WITH first_activity AS (
  SELECT
    user_id,
    date_trunc('week', MIN(timestamp_utc)) AS cohort_week
  FROM product_events
  WHERE event_name = 'first_value_completed'
  GROUP BY user_id
),
weekly_activity AS (
  SELECT DISTINCT
    user_id,
    date_trunc('week', timestamp_utc) AS activity_week
  FROM product_events
  WHERE event_name = 'core_feature_used'
),
cohort_activity AS (
  SELECT
    f.cohort_week,
    date_part('day', a.activity_week - f.cohort_week) / 7 AS week_number,
    COUNT(DISTINCT a.user_id) AS retained_users
  FROM first_activity f
  JOIN weekly_activity a ON f.user_id = a.user_id
  WHERE a.activity_week >= f.cohort_week
  GROUP BY
    f.cohort_week,
    date_part('day', a.activity_week - f.cohort_week) / 7
),
cohort_sizes AS (
  SELECT cohort_week, COUNT(*) AS cohort_size
  FROM first_activity
  GROUP BY cohort_week
)
SELECT
  c.cohort_week,
  c.week_number,
  c.retained_users,
  s.cohort_size,
  100.0 * c.retained_users / NULLIF(s.cohort_size, 0) AS retention_pct
FROM cohort_activity c
JOIN cohort_sizes s ON c.cohort_week = s.cohort_week
WHERE c.week_number BETWEEN 0 AND 12
ORDER BY c.cohort_week, c.week_number;