Growth and Product Metrics Analysis Questions

WITH users_cohort AS (
  SELECT
    user_id,
    date_trunc('week', created_at)::date AS cohort_week
  FROM users
),
events_week AS (
  SELECT
    e.user_id,
    date_trunc('week', e.occurred_at)::date AS event_week
  FROM events e
  WHERE e.occurred_at >= (SELECT min(created_at) FROM users) -- optional filter
),
user_activity AS (
  -- join users to events and compute week offset relative to cohort
  SELECT
    u.user_id,
    u.cohort_week,
    EXTRACT(EPOCH FROM (ew.event_week - u.cohort_week)) / 86400 / 7 AS week_number_float
  FROM users_cohort u
  LEFT JOIN events_week ew ON u.user_id = ew.user_id
),
user_week AS (
  -- convert to integer week number and keep only 0..11
  SELECT
    user_id,
    cohort_week,
    FLOOR(COALESCE(week_number_float, 0))::int AS week_number
  FROM user_activity
  WHERE FLOOR(COALESCE(week_number_float, 0)) BETWEEN 0 AND 11
  GROUP BY user_id, cohort_week, FLOOR(COALESCE(week_number_float, 0))
),
cohort_sizes AS (
  SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM users_cohort
  GROUP BY cohort_week
),
cohort_retention AS (
  SELECT
    uw.cohort_week,
    uw.week_number,
    COUNT(DISTINCT uw.user_id) AS retained_users
  FROM user_week uw
  GROUP BY uw.cohort_week, uw.week_number
)
SELECT
  cs.cohort_week,
  gs.week_number,
  cs.cohort_size,
  COALESCE(cr.retained_users, 0) AS retained_users,
  ROUND(COALESCE(cr.retained_users,0)::numeric / cs.cohort_size, 4) AS retention_rate
FROM cohort_sizes cs
-- generate rows for weeks 0..11 to ensure full matrix
CROSS JOIN LATERAL (
  SELECT generate_series(0,11) AS week_number
) gs
LEFT JOIN cohort_retention cr
  ON cr.cohort_week = cs.cohort_week AND cr.week_number = gs.week_number
ORDER BY cs.cohort_week, gs.week_number;

-- Parameters
-- :start_date, :end_date define observation window (e.g., cohort signup window and max follow-up)
WITH cohorts AS (
  SELECT
    u.user_id,
    date_trunc('week', u.created_at)::date AS cohort_week,
    generate_series(0, floor(EXTRACT(epoch FROM (:end_date::timestamp - u.created_at::timestamp))/604800)::int) AS max_followup_weeks
  FROM users u
  WHERE u.created_at >= :start_date AND u.created_at < :end_date
),
first_event AS (
  -- first event after signup (exclude events before signup)
  SELECT
    c.user_id,
    c.cohort_week,
    MIN(date_trunc('week', e.occurred_at) - date_trunc('week', c.cohort_week)) / 7 AS week_of_first_event
  FROM cohorts c
  LEFT JOIN events e
    ON e.user_id = c.user_id
    AND e.occurred_at >= c.cohort_week
    AND e.occurred_at <= :end_date
  GROUP BY c.user_id, c.cohort_week
),
user_time AS (
  -- compute time-to-event in weeks (NULL => no event observed => censored at max_followup_weeks)
  SELECT
    c.user_id,
    c.cohort_week,
    CASE
      WHEN fe.week_of_first_event IS NOT NULL THEN CAST(fe.week_of_first_event AS int)
      ELSE c.max_followup_weeks
    END AS time_weeks,
    CASE WHEN fe.week_of_first_event IS NULL THEN true ELSE false END AS censored
  FROM cohorts c
  LEFT JOIN first_event fe USING (user_id, cohort_week)
),
weeks AS (
  -- possible week indices to report (0 = signup week)
  SELECT generate_series(0, (SELECT MAX(max_followup_weeks) FROM cohorts)) AS week_number
),
km_counts AS (
  -- for each week, count at-risk and events (first-event exactly at that week)
  SELECT
    w.week_number,
    COUNT(*) FILTER (WHERE ut.time_weeks >= w.week_number) AS at_risk_count,
    COUNT(*) FILTER (WHERE ut.time_weeks = w.week_number AND NOT ut.censored) AS events_count
  FROM weeks w
  CROSS JOIN (SELECT DISTINCT user_id, time_weeks, censored FROM user_time) ut
  GROUP BY w.week_number
  ORDER BY w.week_number
),
km AS (
  SELECT
    week_number,
    at_risk_count,
    events_count,
    -- running product for survival: survival(0)=1, then multiply (1 - d/n) each week
    exp(sum(ln(1.0 - events_count::float / NULLIF(at_risk_count,0))) OVER (ORDER BY week_number ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS survival_probability
  FROM km_counts
)
SELECT week_number, at_risk_count, events_count,
       COALESCE(survival_probability, 1.0) AS survival_probability
FROM km;