2024 PLwiki eligible voters for Board elections

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 plwiki_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 < '20240804000000' -- edits prior to 4 August 2024
  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 > '20220905000000' -- edits from 6 September 2022
    and rev_timestamp < '20240804000000' -- edits before 4 August 2024
  group by actor_user
  having count(*) >= 15 
) onefive -- at least 15 edits from 6 September 2022 to 4 August 2024
  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...