Untitled query #87998

SQL

with ysocks as (
select actor_id from actor_user where actor_name = 'Icewhiz'
union
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 '%Icewhiz%'
union
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 '%Icewhiz'
),
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 = 'Huldra'
	group by 2
),
yrev as (
	select 'y', ru.rev_page, count(*) editcount
	from 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 'Huldra', 
	yrev.editcount 'Icewhiz+socks' 
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
SQL

Checking query status...
