zhwiki RFX Eligible Voters

SQL

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

每年选举为4/28以及10/28，前推120天为12/29（非闰年，闰年则为12/30）或6/30，前推90天为1/28（闰年1/29）或7/30

第一条大约要15分钟
编辑3000次的query最快
编辑条目至少1500次的query要10-15分钟
合起来有可能会跑到半小时的limit被杀掉？也可以分开跑然后用其他方法union
因为统计这些奇奇怪怪的编辑数要求必须遍历筛选过的revision，所以这么慢，如果有优化方法请联络我
*/

SELECT tmp.actor_name user_name
FROM (
  SELECT actor_id, actor_name, COUNT(*) edit_count
  FROM actor
  JOIN revision_userindex ON rev_actor = actor_id
  JOIN page ON rev_page = page_id
  WHERE rev_timestamp < 20230630000000
  AND actor_user IS NOT NULL
  GROUP BY actor_id
  HAVING edit_count >= 500
) tmp
JOIN revision_userindex ON rev_actor = tmp.actor_id
JOIN page ON rev_page = page_id
WHERE rev_timestamp >= 20230730000000
AND page_namespace != 2
AND page_namespace != 3
GROUP BY tmp.actor_id

UNION

SELECT user_name
FROM user
WHERE user_editcount >= 3000

UNION

SELECT actor_name user_name
FROM actor
JOIN revision_userindex ON rev_actor = actor_id
JOIN page ON rev_page = page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
GROUP BY actor_id
HAVING COUNT(*) >= 1500

ORDER BY user_name;
SQL

Checking query status...
