use fiwiki_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;