query.sql

EXPLAIN ANALYZE
SELECT
  hrp1.id
FROM http_request_plan hrp1
LEFT JOIN LATERAL (
  SELECT
    count(*) request_count,
    CASE
      WHEN count(*) = 0
      THEN NULL
      ELSE count(*) FILTER (WHERE most_recent_request_report.request_is_successful = TRUE)::numeric / count(*)
    END successful_request_rate
  FROM (
    SELECT
      chr1.http_request_plan_id,
      chr1.request_is_successful
    FROM cinema_http_request chr1
    WHERE
      chr1.cinema_id = 1000158 AND
      chr1.http_request_plan_id = hrp1.id AND
      chr1.ended_at > now() - interval '1 hour'
    ORDER BY
      chr1.ended_at DESC
    LIMIT 100
  ) most_recent_request_report
  GROUP BY
    most_recent_request_report.http_request_plan_id
) AS running_report ON TRUE
WHERE
  hrp1.id != ALL('{47}') AND
  hrp1.disabled_at IS NULL AND
  (
    hrp1.cinema_id IS NULL OR
    hrp1.cinema_id = 1000158
  ) AND
  (
    hrp1.country_id IS NULL OR
    hrp1.country_id = 1000012
  ) AND
  (
    running_report.successful_request_rate IS NULL OR
    running_report.successful_request_rate > 0.8 OR
    running_report.request_count IS NULL OR
    running_report.request_count < 100
  )
ORDER BY
  hrp1.weight ASC
LIMIT 1