Fork of Last 30d IP vandalism ratio per subnets (fiwiki) by Zache
This query is marked as a draft This query has been published by Jmk.

SQL

AخA
 
use fiwiki_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;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...