Untitled query #38331

SQL

use ruwiki_p;

set @date = cast('20190817' as date);

select
  u.user_id, cast(u.user_name as char)
from user u
join actor a on a.actor_user = u.user_id
join (
  select r.rev_actor, count(1) recent_cnt
  from revision r
  where cast(r.rev_timestamp as date) >= @date - interval 1 month
--  and r.rev_actor = a.actor_id
  group by r.rev_actor
) rc on rc.rev_actor = a.actor_id
left join user_groups g on g.ug_user = u.user_id and g.ug_group = 'bot'
left join (
  select b.ipb_id, b.ipb_user, cast(ipb_expiry as datetime) ipb_expiry
  from ipblocks b
) b on b.ipb_user = u.user_id and (b.ipb_expiry is null or b.ipb_expiry >= @date)
where 
  -- auto-confirmed
  -- rc.cnt >= 15
-- and 
datediff(@date, cast(u.user_registration as date)) >= 4
  -- registered for no more than a year with at least 30 edits in the last month
and rc.recent_cnt >= 30
and cast(u.user_registration as date) >= (@date - interval 1 year) 
  -- not bot
and g.ug_user is null
  -- not blocked on @date
and b.ipb_id is null
limit 9999999
SQL

Checking query status...
