Edit count Irish Nationalism, Unionism in Ireland, The Troubles, articles summary

Fork of Edit count Irish Nationalism articles summary by BilledMammal

This query is marked as a draft This query has been published by BilledMammal.

SQL

WITH RECURSIVE get_name_dabs (page_title, page_id, page_namespace, inc, parent_title, parent_namespace) AS (
  (
    SELECT page_title, page_id, page_namespace, 1, page_title, page_namespace
    FROM page
    WHERE page_title IN ("Irish_nationalism", "Unionism_in_Ireland", "The_Troubles_(Northern_Ireland)")
      AND page_namespace = 14
  )
  UNION
  (
    SELECT child.page_title, child.page_id, child.page_namespace, inc + 1, parent.page_title, parent.page_namespace
    FROM page AS child
    JOIN categorylinks as childLinks ON child.page_id = childLinks.cl_from
    JOIN get_name_dabs AS parent ON childLinks.cl_to = parent.page_title
    LEFT JOIN page_props ON childLinks.cl_from = pp_page AND pp_propname = "hiddencat"
    WHERE (child.page_namespace = 14
      OR child.page_namespace = 0)
      AND parent.page_namespace = 14
      AND pp_propname IS NULL
      #AND inc < 4
  )
),

distinct_name_dabs AS (
  SELECT DISTINCT(page_title), page_id, page_namespace, parent_title, parent_namespace
  FROM get_name_dabs
  WHERE page_namespace = 0
                       

counts AS (SELECT edits, reverted_edits, user_editcount AS total_edits
FROM user
RIGHT JOIN (SELECT actor_user, actor_name, COUNT(*) AS edits, COUNT(ct_tag_id) AS reverted_edits
	FROM get_name_dabs
	JOIN revision ON page_id = rev_page
	JOIN actor ON actor_id = rev_actor
    LEFT JOIN change_tag ON ct_rev_id = rev_id AND (ct_tag_id = 582 OR ct_tag_id = 590)
	WHERE rev_timestamp > 20220000000000
    GROUP BY actor_id) editors ON actor_user = user_id),
            
sums AS (SELECT 
	SUM(edits) as "AllEdits",
	SUM(reverted_edits) as "AllReverts",
	SUM(CASE WHEN total_edits > 499 THEN edits ELSE 0 END) as "EXCEdits",
	SUM(CASE WHEN total_edits > 499 THEN reverted_edits ELSE 0 END) as "EXCReverts",
	SUM(CASE WHEN total_edits < 500 THEN edits ELSE 0 END) as "nonEXCEdits",
	SUM(CASE WHEN total_edits < 500 THEN reverted_edits ELSE 0 END) as "nonEXCReverts",
	SUM(CASE WHEN total_edits > 9 THEN edits ELSE 0 END) as "AutoEdits",
	SUM(CASE WHEN total_edits > 9 THEN reverted_edits ELSE 0 END) as "AutoReverts",
	SUM(CASE WHEN total_edits < 10 THEN edits ELSE 0 END) as "nonAutoEdits",
	SUM(CASE WHEN total_edits < 10 THEN reverted_edits ELSE 0 END) as "nonAutoReverts",
	SUM(CASE WHEN total_edits > 9 AND total_edits < 500 THEN edits ELSE 0 END) as "AutoAndNonEXCEdits",
	SUM(CASE WHEN total_edits > 9 AND total_edits < 500 THEN reverted_edits ELSE 0 END) as "AutoAndNonEXCReverts"
FROM counts)

SELECT 
	AllEdits,
	AllReverts,
    AllReverts / AllEdits AS "% reverts all",
	EXCEdits,
	EXCReverts,
    EXCReverts / EXCEdits AS "% reverts EXC",
	nonEXCEdits,
	nonEXCReverts,
    nonEXCReverts / nonEXCEdits AS "% reverts non-EXC",
	AutoEdits,
	AutoReverts,
    AutoReverts / AutoEdits AS "% reverts auto",
	nonAutoEdits,
	nonAutoReverts,
    nonAutoReverts / nonAutoEdits AS "% reverts non-auto",
	AutoAndNonEXCEdits,
	AutoAndNonEXCReverts,
    AutoAndNonEXCReverts / AutoAndNonEXCEdits AS "% reverts auto, non-EXC",
    EXCEdits / AllEdits AS "% EXC edits",
    AutoEdits / AllEdits AS "% Auto edits"
FROM sums

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...