Untitled query #38345

SQL

use ruwiki_p;

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

with tu as 
(
  select uu.user_id, uu.user_name, ua.actor_id
  from user uu
  join actor ua on ua.actor_user = uu.user_id
  -- registered for no more than a year
  where cast(uu.user_registration as date) >= @date - interval 1 year
  -- auto-confirmed
  and datediff(@date, cast(uu.user_registration as date)) >= 4
),
trc as 
 (
  select ru.user_id, count(1) recent_cnt
  from revision r
  join tu ru on r.rev_actor = ru.actor_id 
  where cast(r.rev_timestamp as date) >= @date - interval 1 month
  group by ru.user_id
  -- with at least 30 edits in the last month
  having recent_cnt >= 30
)
select
  u.user_id, cast(u.user_name as char)
from tu u
join trc rc on rc.user_id = 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
g.ug_user is null
  -- not blocked on @date
and b.ipb_id is null
limit 9999999
SQL

Checking query status...
