Fork of
Untitled query #38331
by Ле Лой
This query is marked as a draft
This query has been published
by Zanka.
SQL
x
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 Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.