Replicas browser and optimizer
Untitled query #38331
This query is marked as a draft
This query has been published
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
By running queries you agree to the
and you irrevocably agree to release your SQL under
All SQL code is licensed under
Checking query status...
Report a bug!
Wikimedia Cloud VPS
, written in