Page intersections between a reference actor and a ban evasion source and their socks

This query is marked as a draft This query has been published by Sean.hoyland.

SQL

-- page intersections between a reference actor and one or more other actors with page edits counts.
-- Edit counts for the other actors are summed e.g. editcounts for multiple ban evasion accounts 
-- with a single sockmaster are summed. 

-- reference account to be compared with one or more other accounts
set @x = 'Owenglyndur'; 

-- sockmaster - ban evasion account operator label used in SPI/logs/category graph, not always the oldest account
set @y = 'Icewhiz';

-- the query - single quotes need to be escaped either by doubling or backslash
set @q = '
with ysocks as (
select actor_id from actor_user where actor_name = ?
union all
-- select
-- a.actor_id
-- from actor_user a
-- join ipblocks b on a.actor_user = b.ipb_user
-- join comment_ipblocks c on b.ipb_reason_id = c.comment_id
-- where
-- convert(c.comment_text using utf8mb4) rlike ''(checkuser|sock|multiple accounts|evasion|proxy)''
-- and convert(c.comment_text using utf8mb4) like concat(''%'',?,''%'')
-- union all
select
a.actor_id
from
categorylinks cl
join page p on cl_from = p.page_id
join category cat on cl.cl_to = cat.cat_title
join actor_user a on a.actor_name = replace(p.page_title,''_'','' '')
where
cl.cl_to like ''%Wikipedia_sockpuppets_of_%''
and
cl.cl_to like concat(''%'',?)
),
xrev as (
	select ''x'' actor, ru.rev_page, count(*) editcount
	from actor_user au
	join revision_userindex ru on au.actor_id = ru.rev_actor and au.actor_name = ?
	group by 2
),
yrev as (
	select ''y'', ru.rev_page, count(*) editcount 
	from (select distinct sql_small_result actor_id from ysocks) ysocks
	join revision_userindex ru on ysocks.actor_id = ru.rev_actor
	group by 2
)
select 
	xrev.rev_page, 
	convert(replace(p.page_title,''_'','' '') using utf8mb4) page_title, 
	p.page_namespace, 
	case
	when p.page_namespace = 0 then ''(Main/Article)''
	when p.page_namespace = 1 then ''Talk''
	when p.page_namespace = 2 then ''User''
	when p.page_namespace = 3 then ''User talk''
	when p.page_namespace = 4 then ''Wikipedia''
	when p.page_namespace = 5 then ''Wikipedia talk''
	when p.page_namespace = 6 then ''File''
	when p.page_namespace = 7 then ''File talk''
	when p.page_namespace = 8 then ''MediaWiki''
	when p.page_namespace = 9 then ''MediaWiki talk''
	when p.page_namespace = 10 then ''Template''
	when p.page_namespace = 11 then ''Template talk''
	when p.page_namespace = 12 then ''Help''
	when p.page_namespace = 13 then ''Help talk''
	when p.page_namespace = 14 then ''Category''
	when p.page_namespace = 15 then ''Category talk''
	when p.page_namespace = 100 then ''Portal''
	when p.page_namespace = 101 then ''Portal talk''
	when p.page_namespace = 118 then ''Draft''
	when p.page_namespace = 119 then ''Draft talk''
	when p.page_namespace = 710 then ''TimedText''
	when p.page_namespace = 711 then ''TimedText talk''
	when p.page_namespace = 828 then ''Module''
	when p.page_namespace = 829 then ''Module talk''
	else null
	end as namespace_desc, 
	case when p.page_is_redirect = 0 then ''No'' else ''Yes'' end page_is_redirect,
	xrev.editcount ''ref actor editcount'', 
	yrev.editcount ''other actor(s) editcount summed'' 
from xrev
join yrev on xrev.rev_page = yrev.rev_page
join page p on xrev.rev_page = p.page_id
order by 2,3';

-- build the statement, execute, deallocate
prepare s from @q;
execute s using @y, @y, @y, @x;
deallocate prepare s;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...