Find database lock waits and deadlocks

SELECT
  database_name,
  blocked_query_fingerprint,
  blocking_query_fingerprint,
  COUNT(*) AS lock_wait_incidents,
  COUNT(DISTINCT transaction_id) AS affected_transactions,
  AVG(wait_ms) AS average_wait_ms,
  MAX(wait_ms) AS maximum_wait_ms,
  SUM(CASE WHEN NOT resolved THEN 1 ELSE 0 END) AS unresolved_incidents,
  SUM(CASE WHEN deadlock_detected THEN 1 ELSE 0 END) AS deadlocks
FROM database_lock_wait_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
  AND environment = 'production'
GROUP BY
  database_name,
  blocked_query_fingerprint,
  blocking_query_fingerprint
HAVING COUNT(*) >= 3
ORDER BY deadlocks DESC, maximum_wait_ms DESC;