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
  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, rev_len, rev_parent_id
  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
),

revisions_delta AS (
  SELECT revision.rev_id, revision.rev_page, actor_id, actor_name, revision.rev_timestamp, page_namespace, registration, GREATEST(CAST(revisions.rev_len AS SIGNED) - CAST(revision.rev_len AS SIGNED),0) as additions
  FROM revisions
  JOIN revision ON revision.rev_id = revisions.rev_parent_id
),

arbpia_revisions AS (
  SELECT 
  	actor_name, 
  	LEFT(rev_timestamp, 6) AS month, 
  	COUNT(*) as arbpia_edits,
  	COUNT(CASE WHEN page_namespace = 0 THEN 1 END) AS arbpia_edits_article,
    COUNT(CASE WHEN page_namespace = 1 THEN 1 END) AS arbpia_edits_talk,
  	SUM(additions) AS arbpia_additions
  FROM revisions_delta
  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 page_namespace = 0 THEN 1 END) AS all_edits_article,
    COUNT(CASE WHEN page_namespace = 1 THEN 1 END) AS all_edits_talk,
  	ecp_month,
  	registration,
  	SUM(additions) AS all_additions
  FROM revisions_delta
  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, arbpia_additions, all_edits, all_edits_article, all_edits_talk, all_additions, ecp_month, 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...
