Untitled query #85985

SQL

WITH talk_pages_projects AS (
    SELECT DISTINCT page_title, page_id
    FROM page
    JOIN categorylinks AS israel ON page_id = israel.cl_from AND israel.cl_to = "WikiProject_Israel_articles"
    JOIN categorylinks AS palestine ON page_id = palestine.cl_from AND palestine.cl_to = "WikiProject_Palestine_articles"
    WHERE page_namespace = 1
   	AND page_is_redirect = 0
),

talk_pages_templates AS (
 	SELECT DISTINCT page_title, page_id
  	FROM page
    JOIN templatelinks ON tl_from = page_id
    JOIN linktarget ON tl_target_id = lt_id
    WHERE page_namespace = 1
    AND page_is_redirect = 0
    AND lt_title in ('ArbCom_Arab-Israeli_enforcement', 'Contentious_topics/Arab-Israeli_talk_notice')
    AND lt_namespace = 10
    AND tl_from_namespace = 1
),

talk_pages AS (
  SELECT DISTINCT page_title, page_id
  FROM talk_pages_projects
  UNION
  SELECT DISTINCT page_title, page_id
  FROM talk_pages_templates
),

arbpia_pages AS (
  SELECT DISTINCT page.page_id
  FROM talk_pages
  JOIN page ON page.page_title = talk_pages.page_title
  WHERE page_namespace = 0
  AND page_is_redirect = 0
  UNION
  SELECT DISTINCT page_id
  FROM talk_pages
),

ecp_actors AS (
  SELECT DISTINCT REPLACE(log_title, "_", " ") as log_title, left(MIN(log_timestamp), 6) AS ecp_month
  FROM logging
  WHERE log_type = "rights"
  AND (log_params LIKE '%extendedconfirmed%'
       OR log_params LIKE '%sysop%')
  GROUP BY log_title
),

topic_actors AS (
  SELECT DISTINCT rev_actor
  FROM arbpia_pages
  JOIN revision ON rev_page = page_id
  WHERE rev_timestamp > 20220000000000 
),

users AS (
  SELECT DISTINCT actor_id, actor_name, ecp_month, LEFT(user_registration, 6) as registration
  FROM actor
  JOIN user ON actor_user = user_id
  LEFT JOIN user_groups ON ug_user = user_id AND ug_group = "bot"
  LEFT JOIN ecp_actors ON actor_name = log_title
  #JOIN user_groups ON ug_user = user_id AND ug_group = "sysop"
  WHERE actor_name IN ("Levivich", "האופה", "Bluethricecreamman", "ABHammad", "Selfstudier", "fiveby", "IOHANNVSVERVS", "Sean.hoyland", "Iskandar323", "Dan Murphy", "Nableezy", "BilledMammal", "Zero0000", "PeleYoetz", "TarnishedPath", "Nishidani", "DMH223344", "M.Bitton")
  #WHERE actor_id IN (SELECT rev_actor FROM topic_actors)
  AND ug_group IS NULL
  #AND user_editcount > 500
  #AND user_editcount < 5000
),

revisions AS (
  SELECT rev_id, rev_page, actor_id, actor_name, rev_timestamp, ecp_month, page_namespace, registration, GREATEST(rev_len, 0) as additions
  FROM revision_userindex
  JOIN page ON page_id = rev_page
  JOIN users ON actor_id = rev_actor
  WHERE page_namespace IN (0, 1)
  AND rev_timestamp > 20240828000000
)

SELECT *
FROM revisions
SQL

Checking query status...
