コホートごとのチャーンと再活性化の測定

WITH monthly AS (
  SELECT DISTINCT
    team_id,
    date_trunc('month', timestamp_utc) AS activity_month
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '180 days'
    AND event_name IN ('feature_used', 'query_run', 'dashboard_viewed')
),
transitions AS (
  SELECT
    activity_month,
    team_id,
    LAG(activity_month) OVER (
      PARTITION BY team_id ORDER BY activity_month
    ) AS previous_active_month
  FROM monthly
)
SELECT
  activity_month,
  SUM(CASE
    WHEN previous_active_month = activity_month - INTERVAL '1 month' THEN 1 ELSE 0
  END) AS retained_teams,
  SUM(CASE
    WHEN previous_active_month < activity_month - INTERVAL '1 month' THEN 1 ELSE 0
  END) AS reactivated_teams
FROM transitions
GROUP BY activity_month
ORDER BY activity_month;