Untitled query #87979

SQL

with pia_titles as (
select
p.page_title
from
linktarget lt
join templatelinks tl on tl.tl_target_id = lt.lt_id
join page p on p.page_id = tl.tl_from
where
lt.lt_namespace = 10 -- Template
and lt.lt_title in ("ArbCom_Arab-Israeli_enforcement", "Contentious_topics/Arab-Israeli_talk_notice")
and page_namespace = 1 and page_is_redirect = 0
union
select
page_title
from
page
join categorylinks israel on page_id = israel.cl_from and israel.cl_to = "WikiProject_Israel_articles"
join categorylinks palestine on page_id = palestine.cl_from and palestine.cl_to = "WikiProject_Palestine_articles"
where
page_namespace = 1 and page_is_redirect = 0
),
pia as (
-- add required page_namespace values here. can only match on page_title
-- unioning separate selects for each namespace seems to be faster than doing 'in (0,1,...)' for large queries with thousands of actors.
select
p1.page_id,
p1.page_title,
p1.page_namespace
from
pia_titles pt
join page p1 on p1.page_title = pt.page_title
and p1.page_namespace in (0,1)
and p1.page_is_redirect = 0
),
pia_final as (
select
'PIA' area,
ar.actor_id,
convert(ar.actor_name using utf8mb4) actor_name,
pia.page_namespace,
count(r.rev_id) AS rev_count,
SUM(CASE WHEN ctd.ctd_name = 'mw-reverted' THEN 1 ELSE 0 END) AS reverted,
SUM(CASE WHEN ctd.ctd_name in ('mw-undo','mw-rollback') THEN 1 ELSE 0 END) AS reverts
from
actor_revision as ar
join revision_userindex as r on r.rev_actor = actor_id
join pia on pia.page_id = r.rev_page
left join change_tag ON r.rev_id = change_tag.ct_rev_id 
left join change_tag_def AS ctd ON change_tag.ct_tag_id = ctd.ctd_id
where
  date(r.rev_timestamp) BETWEEN '2020-10-07' AND '2024-12-31'
  and 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', 'KasiaNhersL', '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', 'Терпение не ненавижу')
  group by actor_name,page_namespace 
)
select
 actor_name,
page_namespace,
rev_count,
(rev_count / (4515.63 - 460.6 * page_namespace)) as percent,
reverts,
reverted,
(100.0 * reverted/rev_count) as percent_reverted
from
pia_final
order by page_namespace,rev_count desc
SQL

Checking query status...
