trwiki:Last 30d IP vandalism ratio per subnets

SQL

use trwiki_p;
SELECT 
   reverts_c, 
   edits_c, 
   revert_address_count,
   edit_address_count,
   reverts_c/edits_c AS revert_edit_ratio, 
   reverts_ip_range AS ip_range
FROM 
(
   SELECT 
      SUM(1) AS reverts_c, 
      COUNT(DISTINCT REGEXP_SUBSTR(lower(rc_comment),'/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})')) AS revert_address_count,
     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 edit_address_count,
    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...
