Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
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
.
Toggle Highlighting
SQL
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
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...