インシデントの検出と復旧時間を計算する

WITH incident_times AS (
  SELECT
    incident_id,
    service,
    MIN(CASE WHEN event_name = 'impact_started' THEN timestamp_utc END)
      AS impact_started_at,
    MIN(CASE WHEN event_name = 'detected' THEN timestamp_utc END)
      AS detected_at,
    MAX(CASE WHEN event_name = 'resolved' THEN timestamp_utc END)
      AS resolved_at
  FROM incident_lifecycle_events
  WHERE timestamp_utc >= now() - INTERVAL '90 days'
    AND environment = 'production'
  GROUP BY incident_id, service
)
SELECT
  service,
  COUNT(*) AS incidents,
  AVG(date_part('epoch', detected_at - impact_started_at) / 60.0)
    AS average_detection_minutes,
  AVG(date_part('epoch', resolved_at - detected_at) / 60.0)
    AS average_recovery_minutes
FROM incident_times
WHERE impact_started_at IS NOT NULL
  AND detected_at IS NOT NULL
  AND resolved_at IS NOT NULL
GROUP BY service
ORDER BY average_recovery_minutes DESC, service;