Untitled query #85655

SQL

WITH talk_pages AS (
    SELECT DISTINCT page_title, page_id
    FROM page
    JOIN categorylinks ON page_id = cl_from
    WHERE page_namespace = 1
   	AND page_is_redirect = 0
   	AND cl_to IN ('WikiProject_Israel_articles', 'WikiProject_Palestine_articles')
),

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

users AS (
  SELECT DISTINCT actor_id, actor_name
  FROM actor
  WHERE actor_name IN ("Levivich", "האופה", "Bluethricecreamman", "ABHammad", "Selfstudier", "fiveby", "IOHANNVSVERVS", "Sean.hoyland", "Iskandar323", "Dan Murphy", "Nableezy", "BilledMammal", "Zero0000", "PeleYoetz", "TarnishedPath", "Nishidani", "DMH223344", "M.Bitton")
),

revisions AS (
  SELECT rev_id, rev_page, actor_id, actor_name, rev_timestamp
  FROM revision_user
  JOIN page ON page_id = rev_page
  JOIN users ON actor_id = rev_actor
  WHERE page_namespace IN (0, 1)
),

arbpia_revisions AS (
  SELECT actor_name, LEFT(rev_timestamp, 6) AS month, COUNT(*) as arbpia_edits
  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
  FROM revisions
  GROUP BY actor_name, month
)

SELECT all_revisions.actor_name as "Editor", arbpia_revisions.month AS "Month", arbpia_edits, all_edits
FROM arbpia_revisions
JOIN all_revisions ON arbpia_revisions.actor_name = all_revisions.actor_name AND all_revisions.month = arbpia_revisions.month
SQL

Checking query status...
