Uncategorized pages on plwiki

SQL

USE dewiki_p;

SELECT tt.* 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 ("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 ("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 > 300
    AND user_registration IS NOT NULL
    AND datediff(now(), str_to_date(user_registration, "%Y%m%d%H%i%S")) > 60
) AS t
WHERE 
	frp_editComments > 50
    AND frp_uniqueContentPages > 14
    AND frp_totalContentEdits > 300
    AND frp_reviewedEdits > 200
    AND frp_demoted = 0
) 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"
WHERE 
    log_actor IS NULL
GROUP BY frp_user_id
SQL

Checking query status...
