privilege-based revision labeling

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 (
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
-- where p1.page_id = 74998519
-- where p1.page_id = 74998642
)
select
'PIA' area,
ar.actor_id,
convert(ar.actor_name using utf8mb4) actor_name,
ru.rev_timestamp,
timestampdiff(second, lu.log_timestamp, ru.rev_timestamp) seconds_ec_to_rev_timestamp,
case when timestampdiff(second, lu.log_timestamp, ru.rev_timestamp) > 0 then 1 else 0 end ec_at_time_of_rev, 
pia.page_namespace
-- count(ru.rev_id) rev_count
from
actor_revision as ar
join revision_userindex ru on ru.rev_actor = ar.actor_id
join pia on pia.page_id = ru.rev_page
-- join user_groups ug on ar.actor_user = ug.ug_user and ug.ug_group rlike '(sysop|extendedconfirm)'
left join logging_userindex lu on lu.log_actor = ar.actor_id 
and lu.log_type = 'rights' 
and lu.log_title = ar.actor_name 
and lu.log_params rlike '(sysop|extendedconfirm)'
-- and lu.log_params like '%extendedconfirm%'
where
ar.actor_user in (
 	select ug.ug_user
 	from user_groups ug
 	where ug_group = 'bot'
 ) is not true
and 
date(ru.rev_timestamp) between '2023-10-07' and '2024-10-08'
-- and ar.actor_name = 'Pave Paws'
and ar.actor_name in ('Ainty Painty','Pave Paws','24.130.244.5')
-- group by 2,3
SQL

Checking query status...
