Edellisten 30-päivän aikana kumotut ip-muokkaukset artikkeleittain (fiwiki)

SQL

SELECT 
  rc.rc_title,
  revert_days,
  revert_count,
  edit_days,
  edit_count,
  revert_days/edit_days as days_ratio,
  revert_count/edit_count as count_ratio,
  latest_revert,
  afl_c,
  rev_c
FROM
(
  SELECT 
     rc_title,
     SUM(1) AS revert_days,
     SUM(reverts_c) AS revert_count,
     max(reverts_d) as latest_revert
  FROM
  (
    SELECT 
       SUM(1) AS reverts_c, 
       floor(rc_timestamp/1000000) as reverts_d, 
       rc_title
    FROM recentchanges    
    WHERE
       rc_comment REGEXP "(ontributions|uokkaukset)/[0-9]{1,4}[.:][0-9]{1,4}[.:]"       
       AND rc_comment NOT LIKE concat("%", rc_user_text, "%")
       AND rc_source="mw.edit" 
       AND rc_namespace=0
      GROUP BY rc_title, reverts_d
   ) 
   AS rc_tmp
   GROUP BY rc_title
)
AS rc
LEFT JOIN
(
   SELECT 
      SUM(1) AS edit_days,
      SUM(edits_c) AS edit_count,
      rc_title 
   FROM (
     SELECT 
        SUM(1) AS edits_c, 
        floor(rc_timestamp/1000000) as edits_d, 
        rc_title
      
     FROM recentchanges    
     WHERE
        rc_user=0
        AND rc_namespace=0
        GROUP BY rc_title, edits_d
     )
     AS edit_tmp
     GROUP BY rc_title
) 
AS ec 
ON rc.rc_title=ec.rc_title
LEFT JOIN
(
   SELECT 
      SUM(1) AS afl_c, 
      afl_title 
   FROM 
     abuse_filter_log
   WHERE
     afl_timestamp > 20160000000000  
     AND afl_namespace=0
   GROUP BY afl_title
)
AS afl
ON afl_title=rc.rc_title
LEFT JOIN
(
   SELECT 
      SUM(1) AS rev_c, 
      page_title 
   FROM 
      revision,
      page 
   WHERE 
      rev_page=page_id 
      AND page_namespace=0
      AND rev_timestamp > 20160000000000 
      AND rev_comment REGEXP "(ontributions|uokkaukset)/[0-9]{1,4}[.:][0-9]{1,4}[.:]" 
      AND rev_comment NOT LIKE concat("%", rev_user_text, "%") 
   GROUP BY rev_page
)
AS rev
ON rev.page_title=rc.rc_title
ORDER BY revert_days DESC 
LIMIT 100;
SQL

Checking query status...
