ویکی‌پدیا:انتخابات هیئت نظارت/دور هفتم/واجدان شرایط رأی‌دهی

ضعف: حساب دیگرساخته‌ای که به اندازهٔ کافی قدیمی نباشد را هم فهرست می‌کند، توضیح بیشتر در:
بحث ویکی‌پدیا:انتخابات هیئت نظارت/دور هفتم/واجدان شرایط رأی‌دهی/پرسمان

SQL

use fawiki_p;
select concat('[[User:', user_name, '|]]'),
concat(left(log_timestamp, 4), '-', mid(log_timestamp, 5, 2), '-', mid(log_timestamp, 7, 2)) as acct_created,
concat('{{formatnum:', onefifty.cnt, '}}') as article_edits,
concat('{{formatnum:', onehundred.cnt, '}}') as recent_edits
from user
left join logging
on log_user = user_id
and log_type = 'newusers'
join
(
  select rev_user, count(*) cnt
  from revision
  join page
  on page_id = rev_page
  where page_namespace = 0
  and rev_timestamp < '20160827000000'
  group by rev_user
  having count(*) >= 150
) onefifty -- at least 150 edits in main namespace
on user_id = onefifty.rev_user
join
(
  select rev_user, count(*) cnt
  from revision
  join page
  on page_id = rev_page
  where page_namespace = 0
  and rev_timestamp > '20160227000000' -- last 6 months leading to nomination start date
  and rev_timestamp < '20160827000000'
  group by rev_user
  having count(*) >= 100
) onehundred -- at least 100 edits in the main namespace in the last six months leading to the nomination start date
on user_id = onehundred.rev_user
left join
(
  select ug_user
  from user_groups
  where ug_group = 'bot'
) bots
on bots.ug_user = user_id
where bots.ug_user is null
and
(
  -- has no creation log
  log_timestamp is null
  -- created 3 or more months prior to nomination start date
  or log_timestamp < '20160527000000'
)
group by user_id
;

SQL

Checking query status...