Fork of IP revert statistics of linked pages (fiwiki) by Zache
This query is marked as a draft This query has been published by Zache.

SQL

​x
 
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/artikkelit2" 
      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/artikkelit2" 
      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/artikkelit2" 
      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/artikkelit2" 
      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
All SQL code is licensed under CC0 License.

Checking query status...