Pages with overlap of spam blocks

SQL

-- See https://quarry.wmflabs.org/query/56548
SET
  STATEMENT max_statement_time = 60 FOR
WITH spam_blocks AS (
  SELECT
    actor_id AS spam_actor_id
  FROM
    ipblocks
    INNER JOIN comment ON (ipblocks.ipb_reason_id = comment.comment_id)
    INNER JOIN actor ON (ipblocks.ipb_user = actor.actor_user)
  WHERE
    TRUE
    AND ipb_user > 0 -- exclude IPs
    AND ipb_timestamp >= '20210101000000'
    AND (
      LOWER(comment_text) LIKE '%spam%'
      OR LOWER(comment_text) LIKE '%advert%'
      OR LOWER(comment_text) LIKE '%promo%'
    )
    AND LOWER(comment_text) NOT LIKE '%Promotional username%' -- exclude promotional username blocks
)
SELECT
  *
FROM (
SELECT
  rev_page,
  COUNT(DISTINCT spam_actor_id) AS n_actors
FROM
  spam_blocks
  INNER JOIN revision ON (spam_blocks.spam_actor_id = revision.rev_actor)
WHERE
  TRUE
  AND rev_timestamp >= '20210101000000'
GROUP BY
  rev_page
) AS page_actor_counts
WHERE
  n_actors > 1
ORDER BY
  n_actors DESC
SQL

Checking query status...
