Data Interpretation & Dashboard Literacy Questions

WITH users AS (
  SELECT
    user_id,
    DATE(signup_at) AS signup_date,
    FORMAT_DATE('%G-%V', DATE(signup_at)) AS signup_week_iso
  FROM (
    SELECT user_id, MIN(signup_at) AS signup_at
    FROM events
    GROUP BY user_id
  )
),

events_weeks AS (
  SELECT
    user_id,
    FORMAT_DATE('%G-%V', DATE(occurred_at)) AS event_week_iso
  FROM events
  WHERE occurred_at IS NOT NULL
  GROUP BY user_id, event_week_iso
),

cohort AS (
  SELECT
    u.signup_week_iso,
    u.user_id,
    -- next week ISO computed from signup_date + 7 days (handles ISO year boundary)
    FORMAT_DATE('%G-%V', DATE_ADD(u.signup_date, INTERVAL 7 DAY)) AS next_week_iso
  FROM users u
)

SELECT
  c.signup_week_iso AS signup_week,
  SAFE_DIVIDE(SUM(CASE WHEN e.user_id IS NOT NULL THEN 1 ELSE 0 END), COUNT(1)) * 100 AS retention_next_week_pct
FROM cohort c
LEFT JOIN events_weeks e
  ON e.user_id = c.user_id
  AND e.event_week_iso = c.next_week_iso
GROUP BY signup_week
ORDER BY signup_week;