Measure Webhook Latency and Duplicate Rate

SELECT
  provider,
  event_type,
  COUNT(*) AS deliveries,
  SUM(CASE WHEN idempotency_outcome = 'duplicate' THEN 1 ELSE 0 END) AS duplicates,
  SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failures,
  100.0 * SUM(CASE WHEN idempotency_outcome = 'duplicate' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS duplicate_rate_pct,
  approx_percentile_cont(latency_ms, 0.95) AS p95_latency_ms
FROM webhook_deliveries
WHERE timestamp_utc >= now() - INTERVAL '7 days'
GROUP BY provider, event_type
ORDER BY duplicate_rate_pct DESC, failures DESC;