Autopatroll candidates on zhwiki

SQL

SELECT actor_name, COUNT(page_id) AS articles_created
FROM user_groups
JOIN actor ON actor_user = ug_user
JOIN revision_userindex ON rev_actor = actor_id
JOIN page ON page_id = rev_page
WHERE ug_group NOT IN ('autopatrolled', 'autoreviewer', 'patroller', 'sysop')
AND rev_parent_id = 0
AND page_namespace = 0
AND page_is_redirect = 0
AND actor_name IN (
    SELECT actor_name
    FROM (
        SELECT RANK() OVER (ORDER BY sl DESC) rank, actor_name, sl
        FROM (
            SELECT actor_name, user_id, COUNT(*) sl
            FROM revision
            JOIN actor ON rev_actor = actor_id
            JOIN `user` ON actor_user = user_id
            WHERE rev_timestamp > DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%Y%m%d%H%i%s')
            GROUP BY actor_name
        ) aaa
        WHERE NOT EXISTS (
            SELECT * FROM user_groups
            WHERE ug_user = user_id AND ug_group = 'bot'
        )
    ) bbb
)
GROUP BY actor_name
HAVING articles_created > 25
ORDER BY articles_created DESC;
SQL

Checking query status...
