Untitled query #88063

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 (
-- split namespaces
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
-- limit 10
)
select pia.page_title, ru.rev_id, ru.rev_timestamp, group_concat(ctd.ctd_name) ctd_name -- , count(*)
from
revision_userindex ru
join pia on pia.page_id = ru.rev_page
join change_tag ct on ru.rev_id = ct.ct_rev_id
join change_tag_def ctd on ct.ct_tag_id = ctd.ctd_id
where
pia.page_id = 75167816
-- ctd.ctd_name in ('mw-undo', 'mw-rollback','mw-reverted','mw-manual-revert','app-undo')
and
date(ru.rev_timestamp) >= '2023-10-07' -- and '2024-10-06'
group by 2,3
order by 1,2 desc
SQL

Checking query status...
