Learning from Incidents and Post Incident Review Questions

WITH recent AS (
  SELECT *, 
         COALESCE(end_time, now()) - start_time AS mttr_interval
  FROM incidents
  WHERE start_time >= now() - INTERVAL '90 days'
),
root_counts AS (
  SELECT service, root_cause_category, COUNT(*) AS cnt
  FROM recent
  GROUP BY service, root_cause_category
),
top3 AS (
  SELECT service, root_cause_category, cnt,
         ROW_NUMBER() OVER (PARTITION BY service ORDER BY cnt DESC) AS rn
  FROM root_counts
)
SELECT
  r.service,
  COUNT(*) AS incident_count,
  AVG(EXTRACT(EPOCH FROM r.mttr_interval)) AS avg_mttr_seconds,
  STRING_AGG(t.root_cause_category || ' (' || t.cnt || ')', ', ' ORDER BY t.cnt DESC) AS top_root_causes
FROM recent r
LEFT JOIN (
  SELECT service, root_cause_category, cnt
  FROM top3
  WHERE rn <= 3
) t ON r.service = t.service
GROUP BY r.service;

WITH svc_inc AS (
  SELECT service, incident_key, COUNT(*) AS occurrences
  FROM incidents
  WHERE started_at >= now() - interval '90 days'
  GROUP BY service, incident_key
),
repeat_by_service AS (
  SELECT service, SUM(occurrences) FILTER (WHERE occurrences>1) AS repeat_count,
         COUNT(*) AS unique_issues
  FROM svc_inc
  GROUP BY service
),
deploy_counts AS (
  SELECT service, COUNT(*) AS deployments_90d
  FROM deployments
  WHERE deployed_at >= now() - interval '90 days'
  GROUP BY service
)
SELECT r.service,
       r.repeat_count,
       r.unique_issues,
       d.deployments_90d,
       -- recurrence normalized by deployment frequency (avoid div0)
       CASE WHEN COALESCE(d.deployments_90d,0)=0 THEN NULL
            ELSE r.repeat_count::float / d.deployments_90d END AS repeats_per_deploy
FROM repeat_by_service r
LEFT JOIN deploy_counts d USING (service)
ORDER BY repeats_per_deploy DESC NULLS LAST
LIMIT 50;

SELECT p.service,
       COUNT(a.id) FILTER (WHERE a.status='closed') AS closed,
       COUNT(a.id) AS total,
       ROUND(100.0 * SUM(CASE WHEN a.closed_at <= a.created_at + interval '30 days' THEN 1 ELSE 0 END) / NULLIF(COUNT(a.id),0),1) AS pct_closed_within_30d,
       ROUND(100.0 * COUNT(a.id) FILTER (WHERE a.status='closed') / NULLIF(COUNT(a.id),0),1) AS pct_closed
FROM action_items a
JOIN postmortems pm ON a.postmortem_id = pm.id
JOIN incidents p ON pm.incident_id = p.id
GROUP BY p.service
ORDER BY pct_closed_within_30d DESC;