バックグラウンドジョブの再試行ストームを検出する

SELECT
  date_trunc('hour', timestamp_utc) AS hour,
  job_name,
  COUNT(*) AS attempts,
  COUNT(DISTINCT job_id) AS logical_jobs,
  SUM(CASE WHEN attempt > 1 THEN 1 ELSE 0 END) AS retry_attempts,
  100.0 * SUM(CASE WHEN attempt > 1 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS retry_attempt_rate_pct,
  100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS failed_attempt_rate_pct
FROM job_attempts
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY date_trunc('hour', timestamp_utc), job_name
HAVING COUNT(*) >= 20
ORDER BY retry_attempt_rate_pct DESC, attempts DESC;