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.