アカウントアクティベーションファネル ダッシュボード

WITH milestones AS (
  SELECT
    account_id,
    MAX(CASE WHEN event_name = 'source_connected'
      AND status = 'success' THEN 1 ELSE 0 END) AS connected,
    MAX(CASE WHEN event_name = 'query_completed'
      AND status = 'success' THEN 1 ELSE 0 END) AS queried,
    MAX(CASE WHEN event_name = 'dashboard_created'
      AND status = 'success' THEN 1 ELSE 0 END) AS dashboarded
  FROM product_events
  GROUP BY account_id
)
SELECT 'signed_up' AS step, COUNT(*) AS accounts FROM accounts
UNION ALL
SELECT 'source_connected', SUM(connected) FROM milestones
UNION ALL
SELECT 'query_completed', SUM(queried) FROM milestones
UNION ALL
SELECT 'dashboard_created', SUM(dashboarded) FROM milestones;