Analyze Authentication Failure Rate

SELECT
  date_trunc('hour', timestamp_utc) AS hour,
  authentication_method,
  failure_reason,
  COUNT(*) AS attempts,
  COUNT(DISTINCT actor_id) AS affected_actors,
  SUM(CASE WHEN outcome = 'failed' THEN 1 ELSE 0 END) AS failed_attempts,
  100.0 * SUM(CASE WHEN outcome = 'failed' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS failure_rate_pct
FROM authentication_events
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY
  date_trunc('hour', timestamp_utc),
  authentication_method,
  failure_reason
HAVING COUNT(*) >= 20
ORDER BY failed_attempts DESC, failure_rate_pct DESC;