ルートとリリースごとにコア Web バイタルを比較

SELECT
  route_template,
  release,
  COUNT(*) AS samples,
  AVG(CASE WHEN metric_name = 'LCP' THEN metric_value ELSE NULL END) AS avg_lcp_ms,
  AVG(CASE WHEN metric_name = 'INP' THEN metric_value ELSE NULL END) AS avg_inp_ms,
  AVG(CASE WHEN metric_name = 'CLS' THEN metric_value ELSE NULL END) AS avg_cls,
  100.0 * SUM(CASE WHEN passed THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS passing_samples_pct
FROM frontend_vital_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
  AND environment = 'production'
GROUP BY route_template, release
ORDER BY passing_samples_pct ASC, route_template, release;