Untitled query #85655

This query is marked as a draft This query has been published by BilledMammal.
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, 0 as namespace
  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, 1 as namespace
  FROM talk_pages
),

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, LEFT(user_registration,8) as user_registration
  FROM actor
  JOIN user ON actor_user = user_id
  LEFT JOIN user_groups ON ug_user = user_id AND ug_group = "bot"
  #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, user_registration, page_namespace
  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 > 20220000000000
),

arbpia_revisions AS (
  SELECT 
  	actor_name, 
  	LEFT(rev_timestamp, 6) AS month, 
  	COUNT(*) as arbpia_edits,
  	COUNT(CASE WHEN rev_namespace = 0 THEN 1 END) AS arbpia_edits_article,
    COUNT(CASE WHEN rev_namespace = 1 THEN 1 END) AS arbpia_edits_talk
  FROM revisions
  WHERE rev_page IN (SELECT * FROM arbpia_pages)
  GROUP BY actor_name, month
),

all_revisions AS (
  SELECT
  	actor_name, 
  	LEFT(rev_timestamp, 6) AS month, 
  	COUNT(*) as all_edits,
  	COUNT(CASE WHEN rev_namespace = 0 THEN 1 END) AS all_edits_article,
    COUNT(CASE WHEN rev_namespace = 1 THEN 1 END) AS all_edits_talk
  FROM revisions
  GROUP BY actor_name, month
)

SELECT all_revisions.actor_name as "Editor", all_revisions.month AS "Month", arbpia_edits, arbpia_edits_article, arbpia_edits_talk, all_edits, all_edits_article, all_edits_talk, user_registration
FROM all_revisions
LEFT JOIN arbpia_revisions ON arbpia_revisions.actor_name = all_revisions.actor_name AND all_revisions.month = arbpia_revisions.month
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
