zhwiki users who are eligible to vote in RfA (optimized)

SQL

USE zhwiki_p;

# 解任投票联署提出或上任投票开始1个月前，编辑100次或以上并在联署提出或上任投票开始前3个月内至少有一次编辑（不包括任何用户页及用户对话页）
# 这里“解任投票联署提出或上任投票开始”的时间点设为 NOW()，如果需要考察某个特定时间点的结果则将所有 NOW() 替换为对应时间戳即可
SELECT user.user_id, user.user_name, user.user_registration, user.user_editcount FROM user
JOIN (
  SELECT user_id FROM user
  JOIN revision ON rev_user = user_id
  WHERE rev_timestamp <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y%m%d%H%i%s')
  GROUP BY user_id
  HAVING COUNT(user_id) >= 100
) t1 ON t1.user_id = user.user_id
WHERE user.user_id IN (
  SELECT user_id FROM user
  JOIN revision ON rev_user = user_id
  JOIN page ON rev_page = page_id
  WHERE page_namespace <> 2 AND page_namespace <> 3 AND
    rev_timestamp >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%Y%m%d%H%i%s')
  GROUP BY user_id
  HAVING COUNT(user_id) >= 1
)
UNION  # UNION 表示三个条件满足一个即可
# 注册满七天，且编辑3000次或以上
SELECT user_id, user_name, user_registration, user_editcount FROM user
JOIN revision ON rev_user = user_id
GROUP BY user_id
HAVING COUNT(user_id) >= 3000
  AND user_registration <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d%H%i%s')
UNION
# 注册满七天，且编辑1500次条目或以上
SELECT user_id, user_name, user_registration, user_editcount FROM user
JOIN revision ON rev_user = user_id
JOIN page ON rev_page = page_id
WHERE page_namespace = 0
GROUP BY user_id
HAVING COUNT(user_id) >= 1500
  AND user_registration <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d%H%i%s');
SQL

Checking query status...
