アップグレード前に使用されていた機能を検索する

WITH upgrades AS (
  SELECT
    team_id,
    MIN(timestamp_utc) AS upgraded_at
  FROM product_events
  WHERE event_name = 'plan_upgraded'
  GROUP BY team_id
),
pre_upgrade_features AS (
  SELECT DISTINCT
    e.team_id,
    e.feature_name
  FROM product_events e
  JOIN upgrades u ON e.team_id = u.team_id
  WHERE e.event_name = 'feature_used'
    AND e.timestamp_utc < u.upgraded_at
    AND e.timestamp_utc >= u.upgraded_at - INTERVAL '30 days'
)
SELECT
  feature_name,
  COUNT(DISTINCT team_id) AS upgraded_teams_using_feature
FROM pre_upgrade_features
GROUP BY feature_name
ORDER BY upgraded_teams_using_feature DESC;