Untitled query #87997

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

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 = 'Huldra'
	group by 2
),
yrev as (
	select 'y', ru.rev_page, count(*) editcount 
	from (
		select actor_id 
		from actor_user 
        where actor_name in (
          '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','Терпение не ненавижу'
        )
--        where actor_id in ('11019','197346090','198247087','203948590','198565686','197770917','204797057','204673156','209810360','201279115','198475047','199252985','200357577','210047931','208309797','215783382','210204452','220726154','209705713','203551516','197894052','208547857','199365610','196837252','226547026','229124395','229848387','228430083','228906011','230454654','231014984','228952532','229784434','205298791','205500663','213714240','213715335','213713574','216361854','200821325','212191937','197376995','198100239','200435854','204944167','199086999','201273247','210364967')
    ) 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
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...
