Ranges /16 per number of open proxy blocks

SQL

SET STATEMENT max_statement_time = 60 FOR
WITH proxy_blocks AS (
SELECT
  ip16,
  c
FROM
  (
    SELECT
      ip16,
      count(*) AS c
    FROM
      (
        SELECT
          DISTINCT ipb_address,
          REGEXP_REPLACE(ipb_address, '\.[0-9]+\.[0-9]+(/[0-9]+)?$', '.0.0/16') AS ip16
        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 '%/16'
          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%'
          )
      ) AS q1
    GROUP BY
      ip16
  ) AS q2
WHERE
  TRUE
  AND c >= 2
),
recent_ranges AS (
  SELECT
    DISTINCT REGEXP_REPLACE(actor_name, '\.[0-9]+\.[0-9]+$', '.0.0/16') AS ip16
  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.ip16,
  c
FROM
  proxy_blocks
  INNER JOIN recent_ranges ON proxy_blocks.ip16 = recent_ranges.ip16
ORDER BY
  c DESC,
  proxy_blocks.ip16 ASC
SQL

Checking query status...
