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.