アクティベーションまでの時間を測定する

WITH milestones AS (
  SELECT
    user_id,
    MIN(CASE
      WHEN event_name = 'signup_completed' THEN timestamp_utc
    END) AS signed_up_at,
    MIN(CASE
      WHEN event_name = 'first_meaningful_action_completed' THEN timestamp_utc
    END) AS activated_at
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '90 days'
  GROUP BY user_id
)
SELECT
  CASE
    WHEN activated_at - signed_up_at < INTERVAL '10 minutes' THEN 'under 10m'
    WHEN activated_at - signed_up_at < INTERVAL '1 hour' THEN '10m–1h'
    WHEN activated_at - signed_up_at < INTERVAL '1 day' THEN '1h–1d'
    WHEN activated_at - signed_up_at < INTERVAL '7 days' THEN '1d–7d'
    ELSE '7d+'
  END AS activation_window,
  COUNT(*) AS users
FROM milestones
WHERE signed_up_at IS NOT NULL
  AND activated_at >= signed_up_at
GROUP BY activation_window
ORDER BY users DESC;