Turning data into
strategic decisions

-- Monthly Cohort Retention Analysis
WITH monthly_activity AS (
  SELECT
    user_id,
    DATE_TRUNC('month', event_date)   AS active_month,
    COUNT(*)                            AS total_events
  FROM events
  WHERE event_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY 1, 2
),
first_seen AS (
  SELECT user_id, MIN(active_month) AS cohort_month
  FROM monthly_activity
  GROUP BY user_id
)
SELECT
  fs.cohort_month,
  COUNT(DISTINCT fs.user_id)                          AS cohort_size,
  COUNT(DISTINCT ma.user_id)                          AS retained_users,
  ROUND(
    COUNT(DISTINCT ma.user_id) * 100.0
    / NULLIF(COUNT(DISTINCT fs.user_id), 0), 2
  )                                                      AS retention_pct
FROM first_seen fs
LEFT JOIN monthly_activity ma ON fs.user_id = ma.user_id
GROUP BY fs.cohort_month
ORDER BY fs.cohort_month;