インシデントの顧客への影響を計画ごとに測定する

SELECT
  incident_id,
  plan,
  COUNT(*) AS assessed_accounts,
  SUM(CASE WHEN affected THEN 1 ELSE 0 END) AS affected_accounts,
  100.0 * SUM(CASE WHEN affected THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS affected_accounts_pct,
  AVG(CASE WHEN affected THEN impact_duration_ms ELSE NULL END)
    AS avg_impact_duration_ms
FROM incident_account_impact_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
  AND environment = 'production'
GROUP BY incident_id, plan
ORDER BY affected_accounts DESC, plan;