Detect N+1 Database Query Patterns

WITH queries_per_request AS (
  SELECT
    route_template,
    query_fingerprint,
    request_id,
    COUNT(*) AS executions_in_request
  FROM database_request_query_events
  WHERE timestamp_utc >= now() - INTERVAL '24 hours'
    AND environment = 'production'
  GROUP BY route_template, query_fingerprint, request_id
)
SELECT
  route_template,
  query_fingerprint,
  COUNT(*) AS requests,
  SUM(CASE WHEN executions_in_request >= 5 THEN 1 ELSE 0 END)
    AS requests_with_repeats,
  AVG(executions_in_request) AS average_executions_per_request,
  MAX(executions_in_request) AS maximum_executions_in_request
FROM queries_per_request
GROUP BY route_template, query_fingerprint
ORDER BY requests_with_repeats DESC, route_template;