FlaggedRevs editor autopromote list phab:T237191

Fork of FlaggedRevs editor autopromote list phab:T237191 by Zache

This query is marked as a draft This query has been published by Wickie37.

Note: benchmarks is done by unique_days_with_edits

SQL

USE dewiki_p;

SET @editComments = 50;
SET @uniqueContentPages = 14;
SET @totalContentEdits = 300;
SET @reviewedEdits = 200;
SET @revertedEditsRatio = 0.03;
SET @usergroup="editor";
SET @userregistratioage=60;
SET @unique_days_with_edits=45;
-- 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, "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, "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_revision 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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...