Measure whether users return on a specific day after joining.

-- D1 retention; observation cutoff is 2025-01-15

SELECT
  v.signup_at AS cohort_day,
  COUNT(*) AS cohort_size,
  SUM(CASE
    WHEN EXISTS(
      SELECT 1
      FROM watch_events w
      WHERE w.viewer_id=v.id
        AND date(w.watched_at)=date(v.signup_at,'+1 days')
    ) THEN 1
    ELSE 0
  END) AS retained_viewers,
  ROUND(100.0*SUM(CASE
    WHEN EXISTS(
      SELECT 1
      FROM watch_events w
      WHERE w.viewer_id=v.id
        AND date(w.watched_at)=date(v.signup_at,'+1 days')
    ) THEN 1
    ELSE 0
  END)/COUNT(*),2) AS retention_pct
FROM viewers v
WHERE v.signup_at<=date('2025-01-15','-1 days')
GROUP BY v.signup_at