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_revision ON (ipblocks.ipb_user = actor_revision.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
  n_actors,
  n_revs,
  rev_page,
  page_namespace,
  page_title
FROM
  (
    SELECT
      rev_page,
      COUNT(*) AS n_revs,
      COUNT(DISTINCT spam_actor_id) AS n_actors
    FROM
      spam_blocks
      INNER JOIN revision_userindex ON (spam_blocks.spam_actor_id = revision_userindex.rev_actor)
    WHERE
      TRUE
      AND rev_timestamp >= '20210101000000'
    GROUP BY
      rev_page
  ) AS page_actor_counts
  INNER JOIN page ON (page_actor_counts.rev_page = page.page_id)
WHERE
  TRUE
  AND n_actors > 1
  AND page_namespace IN (0, 1, 118, 119)
ORDER BY
  n_actors DESC, n_revs DESC
SQL

Checking query status...
