Measure background job retry and failure rate

SELECT
  job_name,
  COUNT(*) AS attempts,
  SUM(CASE WHEN attempt > 1 THEN 1 ELSE 0 END) AS retries,
  SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failures,
  100.0 * SUM(CASE WHEN attempt > 1 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS retry_rate_pct,
  approx_percentile_cont(duration_ms, 0.95) AS p95_duration_ms
FROM job_runs
WHERE timestamp_utc >= now() - INTERVAL '7 days'
GROUP BY job_name
ORDER BY retry_rate_pct DESC, failures DESC;