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 (

            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