Measure API 429 Rate-Limit Recovery

WITH request_outcomes AS (
  SELECT
    route_template,
    request_id,
    COUNT(*) AS attempts,
    MAX(CASE WHEN status_code = 429 THEN 1 ELSE 0 END) AS was_rate_limited,
    MAX(CASE WHEN status_code BETWEEN 200 AND 299 THEN 1 ELSE 0 END) AS succeeded
  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(was_rate_limited) AS rate_limited_requests,
  SUM(CASE
    WHEN was_rate_limited = 1 AND succeeded = 1 THEN 1
    ELSE 0
  END) AS recovered_requests,
  100.0 * SUM(CASE
    WHEN was_rate_limited = 1 AND succeeded = 1 THEN 1
    ELSE 0
  END) / NULLIF(SUM(was_rate_limited), 0) AS recovery_rate_pct
FROM request_outcomes
GROUP BY route_template
ORDER BY rate_limited_requests DESC, route_template;