Measure Webhook End-to-End Completion

WITH deliveries AS (
  SELECT
    delivery_id,
    provider,
    event_type,
    MIN(CASE
      WHEN event_name = 'webhook_received' THEN timestamp_utc
      ELSE NULL
    END) AS received_at,
    MIN(CASE
      WHEN event_name = 'downstream_completed' AND status = 'success'
      THEN timestamp_utc ELSE NULL
    END) AS completed_at
  FROM webhook_lifecycle_events
  WHERE timestamp_utc >= now() - INTERVAL '7 days'
  GROUP BY delivery_id, provider, event_type
),
delivery_outcomes AS (
  SELECT
    delivery_id,
    provider,
    event_type,
    received_at,
    completed_at,
    date_part('second', completed_at - received_at) AS completion_seconds
  FROM deliveries
  WHERE received_at IS NOT NULL
)
SELECT
  provider,
  event_type,
  COUNT(*) AS received_deliveries,
  SUM(CASE WHEN completed_at IS NOT NULL THEN 1 ELSE 0 END)
    AS completed_deliveries,
  100.0 * SUM(CASE
    WHEN completion_seconds <= 300.0 THEN 1 ELSE 0
  END) / NULLIF(COUNT(*), 0) AS completed_within_target_pct,
  approx_percentile_cont(completion_seconds, 0.95) AS p95_completion_seconds
FROM delivery_outcomes
GROUP BY provider, event_type
HAVING COUNT(*) >= 20
ORDER BY completed_within_target_pct, received_deliveries DESC;