ルートごとにブラウザのロングタスク率を計算する

SELECT
  route_template,
  release,
  COUNT(*) AS observed_tasks,
  SUM(CASE WHEN duration_ms > 50 THEN 1 ELSE 0 END) AS long_tasks,
  SUM(CASE WHEN duration_ms > 50 THEN duration_ms - 50 ELSE 0 END)
    AS blocked_time_ms,
  100.0 * SUM(CASE WHEN duration_ms > 50 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS long_task_rate_pct
FROM browser_task_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
GROUP BY route_template, release
ORDER BY blocked_time_ms DESC;