zhwiki RFX Eligible Voters for the new policy with edit count details

SQL

/*  2024年7月5日 (五) 通过的人事任免投票资格。
注册满120天，解任投票联署提出或上任投票开始120天前，编辑至少500次；并在联署提出或上任投票开始前90天内至少有1次编辑（不包括任何用户页及用户对话页）
OR
注册满120天, 编辑至少3000次
OR
注册满120天, 编辑条目至少1500次
*/

SET @VOTE_TIME = 20241025000000;
SET @PRIOR_90_VOTE_TIME = 20240727000000;
SET @PRIOR_120_VOTE_TIME = 20240627000000;

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
),

user_edits_before_vote AS (
    SELECT actor_id, COUNT(*) AS edits
    FROM actor
    JOIN all_revision ON actor_id = all_revision.rev_actor
    WHERE rev_timestamp < @VOTE_TIME
    GROUP BY actor_id
),

user_edits_before_120_days AS (
    SELECT actor_id, COUNT(*) AS b120edits
    FROM actor
    JOIN all_revision ON actor_id = all_revision.rev_actor
    WHERE rev_timestamp < @PRIOR_120_VOTE_TIME
    GROUP BY actor_id
),

user_edits_in_last_90_days AS (
    SELECT actor_id, COUNT(*) AS 90d_edits
    FROM actor
    JOIN all_revision ON actor_id = all_revision.rev_actor
    JOIN page ON page_id = rev_page
    WHERE rev_timestamp BETWEEN @PRIOR_90_VOTE_TIME AND @VOTE_TIME
      AND page_namespace NOT IN (2, 3)
    GROUP BY actor_id
),

user_edits_page_namespace0 AS (
    SELECT actor_id, 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
)

SELECT actor.actor_id, actor_name, user_registration, user_editcount, uebv.edits, ns0.ns0_edits, ueb120.b120edits, ue90.90d_edits
FROM actor
JOIN user ON actor_user = user_id
JOIN user_edits_before_vote uebv ON actor.actor_id = uebv.actor_id
LEFT JOIN user_edits_before_120_days ueb120 ON actor.actor_id = ueb120.actor_id
LEFT JOIN user_edits_page_namespace0 ns0 ON actor.actor_id = ns0.actor_id
JOIN user_edits_in_last_90_days ue90 ON actor.actor_id = ue90.actor_id
WHERE (user_registration <= @PRIOR_120_VOTE_TIME OR user_registration = NULL)
  AND ( (ueb120.b120edits >= 500 AND ue90.90d_edits > 0) OR (uebv.edits >= 3000) OR (ns0.ns0_edits >= 1500) )
ORDER BY actor_name;
SQL

Checking query status...
