Measure Webhook Retry Recovery

WITH delivery_outcomes AS (
  SELECT
    delivery_id,
    provider,
    event_type,
    MAX(attempt) AS attempts,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_attempts,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS successful_attempts,
    SUM(CASE WHEN status = 'deduplicated' THEN 1 ELSE 0 END) AS deduplicated_attempts
  FROM webhook_deliveries
  WHERE timestamp_utc >= now() - INTERVAL '7 days'
  GROUP BY delivery_id, provider, event_type
)
SELECT
  provider,
  event_type,
  COUNT(*) AS deliveries,
  SUM(CASE
    WHEN failed_attempts > 0 AND successful_attempts > 0 THEN 1 ELSE 0
  END) AS recovered,
  SUM(CASE
    WHEN failed_attempts > 0 AND successful_attempts = 0 THEN 1 ELSE 0
  END) AS permanent_failures,
  SUM(deduplicated_attempts) AS duplicates_suppressed
FROM delivery_outcomes
GROUP BY provider, event_type
ORDER BY permanent_failures DESC, recovered DESC;