Last 30d IP vandalism ratio per subnets (fiwiki)

SQL

use fiwiki_p;
SELECT 
   reverts_c, 
   edits_c, 
   reverts_c/edits_c AS ratio, 
   reverts_ip_range AS ip_range
FROM 
(
   SELECT 
      SUM(1) AS reverts_c, 
     CONCAT(SUBSTR(REGEXP_SUBSTR(lower(rc_comment),'/([0-9]{1,3}\\.[0-9]{1,3}\\.)'),2),"?.?") AS reverts_ip_range 
  FROM recentchanges 
  WHERE 
     rc_comment REGEXP "(ontributions|uokkaukset)/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})" 
     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-9]{1,3}\\.[0-9]{1,3}\\.)'),1),"?.?") AS edits_ip_range 
  FROM recentchanges 
  WHERE 
     rc_user_text REGEXP "^([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})" 
     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...
