Untitled query #87968

This query is marked as a draft This query has been published by Zero0000.

PIA play stats

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 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_Lebanon_articles"
where
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_Syria_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','mw-manual-revert') 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 '2023-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','Starship.paint','Stephan rostie','Vice regent',
       'WillowCity','Sean.hoyland','Galamore','Greyshark09','FortunateSons','Vegan416','M.Bitton',
       'Kowal2701','Coretheapple','Borgenland','Jpesch95','IvanScrooge98','Pachu Kannan','Tombah',
       'Durranistan','XTheBedrockX','Bobfrombrockley','Cdjp1','Dimadick','Supreme Deliciousness','Achmad Rachmani',
       'Buidhe','Pincrete','Rajoub570','TarnishedPath','Bob drobbs','Number 57','Jeppiz','Coretheapple',
       'Sameboat','Tryptofish')
  group by actor_name,page_namespace
)
select
 actor_name as editor,
 page_namespace as namespace,
 rev_count as edits,
-- FORMAT(rev_count / (2579.11 - 1447.08 * page_namespace),2) as percent, -- these numbers need to adjusted for totals
-- reverts,
 CONCAT(FORMAT(100.0 * reverts/rev_count,2),'%') as reverts,
-- reverted,
 CONCAT(FORMAT(100.0 * reverted/rev_count,2),'%') as reverted
from
pia_final
order by page_namespace,rev_count desc

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...