Detect Suspicious Authentication Bursts

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