Untitled query #87992

SQL

set @reverted = 590;
set @undo = 1;
set @rollback = 8;

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_next as (
select
page_namespace,
rev_id,
MAX(CASE WHEN ct_tag_id = @reverted THEN 1 ELSE 0 END) AS pia_reverted,
MAX(CASE WHEN ct_tag_id in (@undo,@rollback) THEN 1 ELSE 0 END) AS pia_revert
from pia
join revision_userindex as r on r.rev_page = pia.page_id
left join change_tag as ct ON r.rev_id = ct.ct_rev_id
where
  date(r.rev_timestamp) BETWEEN '2023-10-07' AND '2024-10-06'
  group by page_namespace,rev_id
),
pia_final as (
select
 page_namespace,
  count(*) as rev_count,
  SUM(pia_reverted) AS reverted,
  SUM(pia_revert) AS reverts,
  SUM(CASE WHEN pia_revert+pia_reverted=2 THEN 1 ELSE 0 END) AS reverted_reverts
 from pia_next
 group by page_namespace
)
select
 page_namespace,
 rev_count,
-- reverts,
 CONCAT(FORMAT(100.0 * reverts/rev_count,2),'%') as reverts,
-- reverted,
 CONCAT(FORMAT(100.0 * reverted/rev_count,2),'%') as reverted,
-- reverts_reverted,
 CONCAT(FORMAT(100.0 * reverted_reverts/rev_count,2),'%') as reverted_reverts
from
pia_final
SQL

Checking query status...
