Fork of zhwiki RFX Eligible Voters by WhitePhosphorus
This query is marked as a draft This query has been published by Stang.

SQL

x
 
/*
解任投票联署提出或上任投票开始120天前,编辑至少500次;并在联署提出或上任投票开始前90天内至少有1次编辑(不包括任何用户页及用户对话页)
OR
编辑至少3000次
OR
编辑条目至少1500次
*/
USE zhwiki_p;
SET @VOTE_TIME = 20231001000000;
SET @PRIOR_90_VOTE_TIME = 20230703000000;
SET @PRIOR_120_VOTE_TIME = 20230603000000;
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 < @VOTE_TIME
      GROUP BY user_w_edit_in_90.actor_id
      HAVING edits >= 500) AS part3
ORDER BY user_name;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...