trwiki:Last 30d IPv6 vandalism ratio per subnets

SQL

use trwiki_p;
SELECT 
   reverts_c, 
   edits_c,
   distinct_addresses_c,
   reverts_c/edits_c AS ratio, 
   reverts_ip_range AS ip_range
FROM 
(
   SELECT 
      SUM(1) AS reverts_c,
     CONCAT(SUBSTR(REGEXP_SUBSTR(rc_comment,'/([0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),2),"*") AS reverts_ip_range 
  FROM recentchanges 
  WHERE 
     rc_comment REGEXP "(ontributions|uokkaukset)/([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}" 
     AND rc_source="mw.edit" 
     GROUP BY reverts_ip_range
) AS reverts 
LEFT JOIN 
(
  SELECT 
     SUM(1) AS edits_c,
     COUNT(DISTINCT rc_user_text) AS distinct_addresses_c,
    CONCAT(SUBSTR(REGEXP_SUBSTR(rc_user_text,'^([0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),1),"*") AS edits_ip_range 
  FROM recentchanges 
  WHERE 
     rc_user_text REGEXP "^([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}" 
     AND rc_user = 0
     AND rc_source="mw.edit" 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...
