2021 eligible voters for Board elections - FINAL

Query to get lists of eligible voters for 2021 Board elections based on contributions to individuals. Please note that this query doesn't exactly reflect the voting eligibility criteria - while the eligibility criteria counts are across wikis, the counts in this query are based on the selected wiki only.

SQL

use hiwiki_p;
select
  user_name,
  twofifty.cnt as edits,
  onefive.cnt as recent_edits
from user
join 
(
  select
    actor_user,
    count(*) cnt
  from revision_userindex
  join actor_revision
    on rev_actor = actor_id
  join page
    on page_id = rev_page
  where
    rev_timestamp < '20210705000000' -- edits prior to 05 July 2021
  group by actor_user
  having count(*) >= 250 -- at least 250 edits in main namspace
  -- the actual criteria is 300 edits across all wikis, but since this query only checks on a single wiki, the criteria is lowered a bit.
) twofifty
  on user_id = twofifty.actor_user
join
(
  select
    actor_user,
    count(*) cnt
  from revision_userindex
  join actor_revision
    on rev_actor = actor_id
  join page
    on page_id = rev_page
  where
	rev_timestamp > '20210104000000' -- edits from 05 January 2021
    and rev_timestamp < '20210705000000' -- edits before 05 July 2021
  group by actor_user
  having count(*) >= 15 
) onefive -- at least 15 edits from 05 January 2021 to 05 July 2021 
  on user_id = onefive.actor_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
order by user_name

SQL

Checking query status...