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 (

            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