Untitled query #33223

SQL

use ruwiki_p;

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

select
  u.user_id, cast(u.user_name as char)
from user u
left join (
  select r.rev_user, count(1) cnt, sum(if(cast(r.rev_timestamp as date) >= (@date - interval 1 month), 1, 0)) recent_cnt
  from revision r
  join page p on p.page_id = r.rev_page
  group by r.rev_user
) rc on rc.rev_user = u.user_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...
