Pages with overlap of spam blocks

SQL

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_id)
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 DISTINCT
  rev_page
FROM
  spam_blocks
  INNER JOIN revision ON (spam_blocks.spam_actor_id = revision.rev_actor)
SQL

Checking query status...
