Measure API 429 rate-limit recovery

WITH request_outcomes AS (
  SELECT
    route_template,
    request_id,
    COUNT(*) AS attempts,
    MIN(CASE WHEN status_code = 429 THEN attempt_number END) AS first_limited_attempt,
    MAX(CASE WHEN status_code BETWEEN 200 AND 299 THEN attempt_number END) AS last_success_attempt
  FROM api_attempt_events
  WHERE timestamp_utc >= now() - INTERVAL '24 hours'
    AND environment = 'production'
  GROUP BY route_template, request_id
)
SELECT
  route_template,
  COUNT(*) AS requests,
  SUM(CASE WHEN first_limited_attempt IS NOT NULL THEN 1 ELSE 0 END) AS rate_limited_requests,
  SUM(CASE
    WHEN last_success_attempt > first_limited_attempt THEN 1
    ELSE 0
  END) AS recovered_requests,
  100.0 * SUM(CASE
    WHEN last_success_attempt > first_limited_attempt THEN 1
    ELSE 0
  END) / NULLIF(SUM(CASE WHEN first_limited_attempt IS NOT NULL THEN 1 ELSE 0 END), 0) AS recovery_rate_pct
FROM request_outcomes
GROUP BY route_template
ORDER BY rate_limited_requests DESC, route_template;