Calculate Database Transaction Rollback Rate

SELECT
  service,
  database_name,
  COUNT(*) AS transactions,
  SUM(CASE WHEN status = 'rolled_back' THEN 1 ELSE 0 END) AS rollbacks,
  100.0 * SUM(CASE WHEN status = 'rolled_back' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS rollback_rate_pct,
  COUNT(DISTINCT CASE
    WHEN status = 'rolled_back' THEN account_id
    ELSE NULL
  END) AS affected_accounts,
  AVG(duration_ms) AS average_duration_ms
FROM database_transaction_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
  AND environment = 'production'
GROUP BY service, database_name
HAVING COUNT(*) >= 10
ORDER BY rollback_rate_pct DESC, transactions DESC;