Untitled query #32375

This query is marked as a draft This query has been published by ظهیری.
SQL

USE fawiki_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 > 20160906000000 
 	  AND rev_timestamp < 20161211000000  
	  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 > 20160906000000 
 	  AND rev_timestamp < 20161211000000  
	  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 > 20160906000000 
 	  AND rev_timestamp < 20161211000000  
	  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 > 20160906000000 
 	  AND rev_timestamp < 20161211000000  
	  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 LIMIT 30;
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...
