Fork of 2021 eligible voters for Board elections - South Asia by KCVelaga (WMF)
This query is marked as a draft This query has been published by Kasyap.

SQL

AخA
 
use tewiki_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(*) >= 300 -- 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(*) >= 20 
) 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
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...