zhwiki RFX Voters (latest, after 20240715)

SQL

/*
解任投票联署提出或上任投票开始120天前，编辑至少500次；并在联署提出或上任投票开始前90天内至少有1次编辑（不包括任何用户页及用户对话页）
OR
编辑至少3000次
OR
编辑条目至少1500次
*/

USE zhwiki_p;
SET @VOTE_TIME = 20240616153600;
SET @PRIOR_90_VOTE_TIME = 20240318153600;
SET @PRIOR_120_VOTE_TIME = 20240217153600;

WITH all_revision AS (SELECT rev_id, rev_actor, rev_page, rev_timestamp
                      FROM revision_userindex
                      UNION
                      SELECT ar_id        AS rev_id,
                             ar_actor     AS rev_actor,
                             ar_page_id   AS rev_page,
                             ar_timestamp AS rev_timestamp
                      FROM archive_userindex)

SELECT part1.actor_name AS user_name
FROM (SELECT actor_user, actor_name, COUNT(*) AS edits
      FROM actor
               JOIN all_revision ON actor_id = all_revision.rev_actor
      WHERE actor_user IS NOT NULL
        AND all_revision.rev_timestamp < @VOTE_TIME
      GROUP BY actor_id
      HAVING edits >= 3000) AS part1

UNION

SELECT part2.actor_name AS user_name
FROM (SELECT actor_id, actor_user, actor_name, COUNT(*) AS ns0_edits
      FROM actor
               JOIN all_revision ON actor_id = all_revision.rev_actor
               JOIN page ON page_id = all_revision.rev_page
      WHERE actor_user IS NOT NULL
        AND all_revision.rev_timestamp < @VOTE_TIME
        AND page_namespace = 0
      GROUP BY actor_id
      HAVING ns0_edits >= 1500) AS part2

UNION

SELECT part3.actor_name AS user_name
FROM (SELECT actor_id, actor_user, actor_name, COUNT(*) AS edits
      FROM (SELECT actor_id, actor_user, actor_name
            FROM actor
                     JOIN all_revision ON actor_id = all_revision.rev_actor
                     JOIN page ON page_id = all_revision.rev_page
            WHERE actor_user IS NOT NULL
              AND all_revision.rev_timestamp >= @PRIOR_90_VOTE_TIME
              AND all_revision.rev_timestamp <= @VOTE_TIME
              AND page_namespace != 2
              AND page_namespace != 3
            GROUP BY actor_id) AS user_w_edit_in_90
               JOIN all_revision ON user_w_edit_in_90.actor_id = all_revision.rev_actor
      WHERE user_w_edit_in_90.actor_user IS NOT NULL
        AND all_revision.rev_timestamp < @PRIOR_120_VOTE_TIME
      GROUP BY user_w_edit_in_90.actor_id
      HAVING edits >= 500) AS part3
ORDER BY user_name;
SQL

Checking query status...
