SQL
x
USE dewiki_p;
SET @editComments = 20;
SET @uniqueContentPages = 8;
SET @totalContentEdits = 150;
SET @reviewedEdits = 50;
SET @revertedEditsRatio = 1;
SET @usergroup="autoreview";
SET @userregistratioage=30;
SET @unique_days_with_edits=21;
-- newver blocked is always true
-- newer demoted is always true
SELECT * FROM (
SELECT tt.*, count(distinct(floor(rev_timestamp/1000000))) as unique_days_with_edits FROM (
SELECT * FROM (
SELECT
user_name,
frp_user_id,
-- frp_editComments
IF (frp_user_params LIKE "%editComments%", CAST(regexp_replace(replace(concat(frp_user_params, ";"), "\n", ";"), ".*?editComments=(.*?);.*$", "\\1") AS UNSIGNED INTEGER), 0) as frp_editComments,
-- frp_uniqueContentPages
IF (frp_user_params LIKE "%uniqueContentPages%", (1+length(regexp_replace(replace(concat(frp_user_params, ";"), "\n", ";"), ".*?uniqueContentPages=(.*?);.*$", "\\1")) - length(replace(regexp_replace(replace(concat(frp_user_params, ";"), "\n", ";"), ".*?uniqueContentPages=(.*?);.*$", "\\1"), ",", ""))), 0) as frp_uniqueContentPages,
-- frp_totalContentEdits
IF (frp_user_params LIKE "%totalContentEdits%", CAST(regexp_replace(replace(concat(frp_user_params, ";"), "\n", ";"), ".*?totalContentEdits=(.*?);.*$", "\\1") AS UNSIGNED INTEGER), 0) as frp_totalContentEdits,
-- frp_reviewedEdits
IF (frp_user_params LIKE "%reviewedEdits%", CAST(regexp_replace(replace(concat(frp_user_params, ";"), "\n", ";"), ".*?reviewedEdits=(.*?);.*$", "\\1") AS UNSIGNED INTEGER), 0) as frp_reviewedEdits,
-- frp_revertedEdits
IF (frp_user_params LIKE "%revertedEdits%", CAST(regexp_replace(replace(concat(frp_user_params, ";"), "\n", ";"), ".*?revertedEdits=(.*?);.*$", "\\1") AS UNSIGNED INTEGER), 0) as frp_revertedEdits,
-- frp_demoted
IF (frp_user_params LIKE "%demoted%", CAST(regexp_replace(replace(concat(frp_user_params, ";"), "\n", ";"), ".*?demoted=(.*?);.*$", "\\1") AS UNSIGNED INTEGER), 0) as frp_demoted,
user_editcount,
datediff(now(), str_to_date(user_registration, "%Y%m%d%H%i%S")) as days_since_registration
FROM
flaggedrevs_promote,
user
LEFT JOIN (SELECT ufg_user FROM user_former_groups WHERE ufg_group IN (@usergroup, "editor", "bot") GROUP BY ufg_user) as ufg ON ufg_user=user_id
LEFT JOIN (SELECT ug_user FROM user_groups WHERE ug_group IN (@usergroup, "editor", "bot") GROUP BY ug_user) as ug ON ug_user=user_id
WHERE
frp_user_id = user_id
AND ufg_user IS NULL
AND ug_user IS NULL
AND user_editcount > @totalContentEdits
AND user_registration IS NOT NULL
AND datediff(now(), str_to_date(user_registration, "%Y%m%d%H%i%S")) > @userregistratioage
) AS t
WHERE
frp_editComments > @editComments
AND frp_uniqueContentPages > @uniqueContentPages
AND frp_totalContentEdits > @totalContentEdits
AND frp_reviewedEdits > @reviewedEdits
AND frp_demoted = 0
AND (frp_revertedEdits/frp_totalContentEdits) < @revertedEditsRatio
) AS tt
LEFT JOIN actor_logging ON tt.frp_user_id=actor_user
LEFT JOIN logging_userindex ON replace(actor_name, " ", "_")=log_title AND log_type="block"
LEFT JOIN revision_userindex ON actor_id = rev_actor
WHERE
log_actor IS NULL
GROUP BY frp_user_id
) AS ttt
WHERE unique_days_with_edits > @unique_days_with_edits;
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.