IP revert statistics of linked pages 3 (fiwiki)

Fork of IP revert statistics of linked pages 3 (fiwiki) by Zache

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

- Timelimit to year 2016 is hardcoded
- total number of edits and reverted edits to namespace 0 is hardcoded (date 2.9.2016)

Columns:
* page_title = page_title
* edits_c = total number of IP edis to selected pages
* reverts_c = total number of reverted IP edits to selected pages
* ratio= reverts_c / edits_c
* reverts_total_= reverts_c / 13439 (total number of reverted ip edits to namespace 0)
* edits_total_r =  edits_c / 77177  (total number of ip edits to namespace 0)

SQL

USE fiwiki_p;
SELECT
  edits.page_title,
  edits.edits_c,
  reverts.reverts_c,
  reverts.reverts_c / edits.edits_c as ratio,
  reverts.reverts_c /13439 as reverts_total_r,
  edits.edits_c / 77177 as edits_total_r,
  (CASE WHEN pr_page IS NULL THEN "" ELSE 1 END) AS suojattu
FROM
(
	SELECT 
	  SUM(1) AS edits_c, 
	  pb.page_title,
	  pb.page_id 
	FROM 
	  page AS pa, 
	  pagelinks, 
	  page as pb, 
	  revision 
	WHERE 
	  pa.page_title="Zache/artikkelit3" 
	  AND pa.page_namespace=2 
	  AND pl_from=pa.page_id 
	  AND pl_from_namespace = pa.page_namespace 
	  AND pl_namespace = 0 
	  AND pb.page_title=pl_title 
	  AND pb.page_namespace=pl_namespace 
	  AND rev_page=pb.page_id 
	  AND rev_timestamp > 20160000000000 
	  AND rev_user=0 
	GROUP BY pb.page_id
) AS edits
LEFT JOIN
(
	SELECT 
	  SUM(1) AS reverts_c, 
	  pb.page_title,
	  pb.page_id 
	FROM 
	  page AS pa, 
	  pagelinks, 
	  page as pb, 
	  revision 
	WHERE 
	  pa.page_title="Zache/artikkelit3" 
	  AND pa.page_namespace=2 
	  AND pl_from=pa.page_id 
	  AND pl_from_namespace = pa.page_namespace 
	  AND pl_namespace = 0 
	  AND pb.page_title=pl_title 
	  AND pb.page_namespace=pl_namespace 
	  AND rev_page=pb.page_id 
	  AND rev_timestamp > 20160000000000 
	  AND rev_comment REGEXP "(ontributions|uokkaukset)/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})" 
          AND NOT (rev_user=0 AND rev_comment LIKE concat("%", rev_user_text, "%")) 
	GROUP BY pb.page_id
) AS reverts
ON reverts.page_id = edits.page_id
LEFT JOIN page_restrictions
ON pr_page=edits.page_id
UNION
SELECT
  edits.page_title,
  edits.edits_c,
  reverts.reverts_c,
  reverts.reverts_c / edits.edits_c as ratio,
  reverts.reverts_c /13439 as r_total_r,
  edits.edits_c / 77177 as r_total_r,
  "" as protected
FROM
(
	SELECT 
	  SUM(1) AS edits_c, 
	  "Yhteensä" as page_title,
	  "" as page_id
	FROM 
	  page AS pa, 
	  pagelinks, 
	  page as pb, 
	  revision 
	WHERE 
	  pa.page_title="Zache/artikkelit3" 
	  AND pa.page_namespace=2 
	  AND pl_from=pa.page_id 
	  AND pl_from_namespace = pa.page_namespace 
	  AND pl_namespace = 0 
	  AND pb.page_title=pl_title 
	  AND pb.page_namespace=pl_namespace 
	  AND rev_page=pb.page_id 
	  AND rev_timestamp > 20160000000000 
	  AND rev_user=0 
) AS edits
LEFT JOIN
(
	SELECT 
	  SUM(1) AS reverts_c, 
	  "Yhteensä" as page_title
	FROM 
	  page AS pa, 
	  pagelinks, 
	  page as pb, 
	  revision 
	WHERE 
	  pa.page_title="Zache/artikkelit3" 
	  AND pa.page_namespace=2 
	  AND pl_from=pa.page_id 
	  AND pl_from_namespace = pa.page_namespace 
	  AND pl_namespace = 0 
	  AND pb.page_title=pl_title 
	  AND pb.page_namespace=pl_namespace 
	  AND rev_page=pb.page_id 
	  AND rev_timestamp > 20160000000000 
	  AND rev_comment REGEXP "(ontributions|uokkaukset)/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})" 
          AND NOT (rev_user=0 AND rev_comment LIKE concat("%", rev_user_text, "%")) 
) AS reverts
ON reverts.page_title = edits.page_title
ORDER BY reverts_c DESC;

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