Ranges /24 per number of open proxy blocks

SQL

SET STATEMENT max_statement_time = 600 FOR
WITH proxy_blocks AS (
SELECT
  ip24,
  c
FROM
  (
    SELECT
      ip24,
      count(*) AS c
    FROM
      (
        SELECT
          DISTINCT ipb_address,
          REGEXP_REPLACE(ipb_address, '\.[0-9]+(/[0-9]+)?$', '.0/24') AS ip24
        FROM
          ipblocks
          INNER JOIN comment ON (ipblocks.ipb_reason_id = comment.comment_id)
        WHERE
          TRUE
          AND ipb_user = 0
          AND ipb_address NOT LIKE '%:%' -- exclude IPv6
          AND ipb_address NOT LIKE '%/24'
          AND comment_text NOT LIKE '%P2P VPN%' -- exclude residential proxies
          AND (
            FALSE
            OR comment_text LIKE '%roxy%'
            OR comment_text LIKE '%roxies%'
            OR comment_text LIKE '%NOP%'
            OR comment_text LIKE '%VPN%'
            OR comment_text LIKE '%colo%'
            OR comment_text LIKE '%webhost%'
          )
          AND ipb_address NOT LIKE '49.228.%' -- exclude problematic Thai range
          AND ipb_address NOT LIKE '175.177.%' -- exclude problematic Japanese range
      ) AS q1
    GROUP BY
      ip24
  ) AS q2
WHERE
  TRUE
  AND c >= 2
),
recent_ranges AS (
  SELECT
    DISTINCT REGEXP_REPLACE(actor_name, '\.[0-9]+$', '.0/24') AS ip24
  FROM
    recentchanges_userindex AS recentchanges
    INNER JOIN actor_recentchanges AS actor ON recentchanges.rc_actor = actor.actor_id
  WHERE
    TRUE
    AND actor_user IS NULL -- exclude registered users
    AND actor_name NOT LIKE '%:%' -- exclude IPv6
)
SELECT
  proxy_blocks.ip24,
  c
FROM
  proxy_blocks
  INNER JOIN recent_ranges ON proxy_blocks.ip24 = recent_ranges.ip24
WHERE
  TRUE
  -- AND proxy_blocks.ip24 NOT IN (SELECT ipb_address FROM ipblocks WHERE ipb_user = 0 AND ipb_address LIKE '%/24')
ORDER BY
  c DESC,
  proxy_blocks.ip24 ASC
SQL

Checking query status...
