インシデントによる顧客への影響 ダッシュボード

WITH account_impact AS (
  SELECT
    incident_id,
    account_id,
    COUNT(*) AS impacted_operations
  FROM service_events
  WHERE incident_id IS NOT NULL
    AND status = 'error'
  GROUP BY incident_id, account_id
)
SELECT
  i.incident_id,
  i.severity,
  COUNT(*) AS affected_accounts,
  SUM(ai.impacted_operations) AS failed_operations,
  SUM(a.monthly_revenue_usd) AS represented_mrr_usd
FROM account_impact ai
JOIN incidents i USING (incident_id)
JOIN accounts a USING (account_id)
GROUP BY i.incident_id, i.severity
ORDER BY failed_operations DESC;