Untitled query #87968

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
union
select
page_title
from
page
join categorylinks ipcoop on page_id = ipcoop.cl_from and ipcoop.cl_to = "WikiProject_Israel_Palestine_Collaboration_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(distinct 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-30'
  and actor_name in ('Arminden','Selfstudier','Iskandar323','Nableezy','Zero0000','Huldra','Nishidani',
       'Levivich','IOHANNVSVERVS','Makeandtoss','האופה','Snowstormfigorion','DMH223344','Ïvana','AnonMoos',
       'Hemiauchenia','Zanahary','BilledMammal','Alaexis','AndreJustAndre','ABHammad','Triggerhippie4',
       'Eladkarmel','Drsmoo','Boud','Onceinawhile','Paul Duffill','Starship.paint','Stephan rostie',
       'WillowCity')
  group by actor_name,page_namespace 
)
select
 actor_name,
 page_namespace,
 rev_count,
-- FORMAT(rev_count / (2579.11 - 1447.08 * page_namespace),2) as percent, -- these numbers need to adjusted for totals
-- reverts,
 FORMAT(100.0 * reverts/rev_count,2) as reverts,
-- reverted,
 FORMAT(100.0 * reverted/rev_count,2) as reverted
from
pia_final
order by page_namespace,rev_count desc
SQL

Checking query status...
