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

​