Last 30d IPv6 vandalism ratio per subnets 2021 (idwiki)

SQL

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(comment_text,'/([0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),2),"*") AS reverts_ip_range 
  FROM recentchanges, comment 
  WHERE 
     rc_comment_id=comment_id
  AND comment_text 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_actor) AS distinct_addresses_c,
    CONCAT(SUBSTR(REGEXP_SUBSTR(rc_actor,'^([0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),1),"*") AS edits_ip_range 
  FROM recentchanges 
  WHERE 
     rc_actor REGEXP "^([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}" 
     AND rc_actor = 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...
