SQL
AخA
use ptwiki_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}\\.[0-9]{1,3}\\.)'),2),"?") AS reverts_ip_range
FROM recentchanges
WHERE
rc_comment REGEXP "(ontributions|ontribuições)/([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}\\.[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.