Cohort Analysis and Retention Metrics Questions

-- Postgres example
WITH cohorts AS (
  SELECT
    user_id,
    install_date,
    date_trunc('week', install_date)::date AS cohort_week
  FROM users
),
days AS (
  -- generate day_n 0..30
  SELECT generate_series(0,30) AS day_n
),
user_days AS (
  -- produce one row per user × day_n and mark if there was an event on that exact day
  SELECT
    c.user_id,
    c.cohort_week,
    d.day_n,
    (c.install_date + d.day_n) AS day_date,
    CASE WHEN e.user_id IS NOT NULL THEN 1 ELSE 0 END AS event_on_day
  FROM cohorts c
  CROSS JOIN days d
  LEFT JOIN events e
    ON e.user_id = c.user_id
   AND e.event_date = (c.install_date + d.day_n)
),
user_days_window AS (
  -- rolling 7-day sum of events per user ordered by day_n (rows between 6 preceding and current)
  SELECT
    user_id,
    cohort_week,
    day_n,
    day_date,
    event_on_day,
    SUM(event_on_day) OVER (
      PARTITION BY user_id
      ORDER BY day_n
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS events_in_last_7_days
  FROM user_days
),
user_retained AS (
  -- user is "retained" on day_n if events_in_last_7_days > 0
  SELECT
    cohort_week,
    day_n,
    user_id,
    CASE WHEN events_in_last_7_days > 0 THEN 1 ELSE 0 END AS retained_flag
  FROM user_days_window
)
SELECT
  cohort_week,
  day_n,
  COUNT(*) AS cohort_size,
  SUM(retained_flag) AS retained_users,
  ROUND(100.0 * SUM(retained_flag) / NULLIF(COUNT(*),0), 2) AS retention_pct
FROM user_retained
GROUP BY cohort_week, day_n
ORDER BY cohort_week, day_n;

-- create canonical user mapping: choose latest active profile
WITH ranked AS (
  SELECT user_id, email, last_active_at,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY last_active_at DESC) rn
  FROM users_raw
)
SELECT user_id AS canonical_user_id, email
INTO user_canonical
FROM ranked WHERE rn = 1;

-- convert string timestamp with timezone info to UTC (Postgres)
SELECT event_id,
       (event_time AT TIME ZONE event_tz) AT TIME ZONE 'UTC' AS event_utc
FROM events_raw;

-- lateness distribution
SELECT DATE_TRUNC('day', event_utc) AS event_day,
       DATE_TRUNC('day', ingest_utc) - DATE_TRUNC('day', event_utc) AS delay_days,
       COUNT(*) FROM events GROUP BY 1,2 ORDER BY 1,2;

WITH events_clean AS (
  SELECT e.*, c.canonical_user_id,
         event_utc::date AS event_date
  FROM events_raw e
  JOIN user_canonical c USING (user_id)
  WHERE e.event_utc IS NOT NULL
)
-- first event per user = cohort_day
, first_event AS (
  SELECT canonical_user_id, MIN(event_date) AS cohort_date
  FROM events_clean
  GROUP BY canonical_user_id
)
SELECT f.cohort_date,
       e.event_date,
       COUNT(DISTINCT e.canonical_user_id) AS users
FROM first_event f
JOIN events_clean e
  ON e.canonical_user_id = f.canonical_user_id
  AND e.event_date BETWEEN f.cohort_date AND f.cohort_date + INTERVAL '30 day'
GROUP BY 1,2;