毎週のコホート維持率の計算

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('epoch', a.activity_week)
      - date_part('epoch', f.cohort_week)) / 604800 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('epoch', a.activity_week)
      - date_part('epoch', f.cohort_week)) / 604800
),
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;