Filtered IPv6 vandalism ratio per subnets (fiwiki)

SQL

use fiwiki_p;
SELECT 
   reverts_c, 
   reverts_d,
   edits_c,
   distinct_addresses_c,
   reverts_c/edits_c AS ratio, 
   reverts_d/edits_d AS ratio2,    
   reverts_ip_range AS ip_range
FROM 
(
   SELECT 
     SUM(1) AS reverts_c,
     count(distinct(floor(rev_timestamp/1000000))) as reverts_d,
     CONCAT(SUBSTR(REGEXP_SUBSTR(rev_comment,'/([0-9A-F]{1,4}:[0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),2),"*") AS reverts_ip_range 
  FROM revision_userindex 
  WHERE 
     rev_timestamp > 20170027223308
     AND rev_comment LIKE "%2001:14BA:%"
     AND rev_comment REGEXP "(ontributions|uokkaukset)/([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}" 
     GROUP BY reverts_ip_range
) AS reverts 
LEFT JOIN 
(
  SELECT 
     SUM(1) AS edits_c,
     count(distinct(floor(rev_timestamp/1000000))) as edits_d,  
     COUNT(DISTINCT rev_user_text) AS distinct_addresses_c,
     CONCAT(SUBSTR(REGEXP_SUBSTR(rev_user_text,'^([0-9A-F]{1,4}:[0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),1),"*") AS edits_ip_range 
  FROM revision_userindex 
  WHERE 
     rev_timestamp > 20170027223308
     AND rev_user_text LIKE "2001:14BA:%"  
     AND rev_user_text REGEXP "^([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}" 
     AND rev_user = 0
     group by edits_ip_range
) AS edits 
ON  edits_ip_range=reverts_ip_range 
ORDER BY reverts_c desc
LIMIT 100;
SQL

Checking query status...
