Insight Translation and Recommendations Questions

WITH cohorts AS (
  SELECT
    user_id,
    date_trunc('week', signup_at AT TIME ZONE 'UTC')::date AS cohort_week
  FROM users
),
cohort_sizes AS (
  SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_users
  FROM cohorts
  GROUP BY cohort_week
),
-- generate 0..3 week offsets per cohort
weeks AS (
  SELECT cohort_week, generate_series(0,3) AS week_number
  FROM (SELECT DISTINCT cohort_week FROM cohorts) cw
),
-- find active users from cohort in each week window
cohort_activity AS (
  SELECT
    w.cohort_week,
    w.week_number,
    COUNT(DISTINCT e.user_id) AS active_users
  FROM weeks w
  JOIN cohorts c ON c.cohort_week = w.cohort_week
  JOIN events e
    ON e.user_id = c.user_id
    -- event occurs within the week_number window after cohort_week
    AND (e.occurred_at AT TIME ZONE 'UTC') >= (w.cohort_week + (w.week_number * INTERVAL '7 days'))
    AND (e.occurred_at AT TIME ZONE 'UTC') <  (w.cohort_week + ((w.week_number + 1) * INTERVAL '7 days'))
  GROUP BY w.cohort_week, w.week_number
)
SELECT
  cs.cohort_week,
  w.week_number,
  COALESCE(ca.active_users, 0) AS active_users,
  cs.cohort_users,
  ROUND(100.0 * COALESCE(ca.active_users, 0) / NULLIF(cs.cohort_users,0), 2) AS retention_rate
FROM weeks w
LEFT JOIN cohort_sizes cs ON cs.cohort_week = w.cohort_week
LEFT JOIN cohort_activity ca ON ca.cohort_week = w.cohort_week AND ca.week_number = w.week_number
ORDER BY cohort_week, week_number;