-- 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 = 'Huldra'; 

-- set @y1 = '007Леони́д', @y2 = '11Fox11';

-- comma separated list of one or more other accounts - single quotes need to be escaped either by doubling or backslash

set @y = '

          ''007Леони́д'',''11Fox11'',''AnnieGrannyBunny'',''Astral Leap'',''AstuteRed'',

          ''Bob not snob'',''DoraExp'',''Double barrel pistol with both opposite direction'',''EnfantDeLaVille'',''Eostrix'',

          ''Free1Soul'',''Galamore'',''Geshem Bracha'',''Herpetogenesis'',''Hippeus'',

          ''I dream of Maple'',''Icewhiz'',''Jacinda01'',''JoeZ451'',''Just Prancing'',

          ''KasiaNL'',''LeftDreams'',''ManoelWild'',''Minden500'',''Molave Quinta'',

          ''Mrboondocks'',''Mvqr'',''O.maximov'',''OdNahlawi'',''PeleYoetz'',

          ''Pikavoom'',''PRL Dreams'',''Proud Indian Arnab'',''Purski'',''RCatesby'',

          ''SCNBAH'',''Seggallion'',''Semper honestus'',''Smoking Ethel'',''SunSun753457'',

          ''Świst lodu'',''Szymon Frank'',''The 2nd coming of Purski'',''UnspokenPassion'',''Uppagus'',

          ''VikingDrummer'',''WhizICE'',''Терпение не ненавижу''

';

​

-- the query - single quotes need to be escaped either by doubling or backslash

set @q = concat('

with 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 actor_id 

        from actor_user 

--        where actor_name in (?, ?)

        where actor_name in (',

@y,

')

    ) 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 @x, @y1, @y2

execute s using @x;

deallocate prepare s;