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

p.page_id, p.page_title, p.page_namespace

from

pia_titles pt

join page p on p.page_title = pt.page_title

and p.page_namespace in (0,1) and p.page_is_redirect = 0

-- where p.page_id = 74998519 -- Israel–Hamas war article

-- where p.page_id = 74998642 -- Israel–Hamas war talk

),

grant_timestamp as (

select log_actor, min(log_timestamp) log_timestamp

from logging_userindex

where log_type = 'rights' 

and log_params rlike '(sysop|extendedconfirmed)'

group by 1

)

select

'PIA' area,

ar.actor_id,

convert(ar.actor_name using utf8mb4) actor_name,

gt.log_timestamp ec_timestamp,

ru.rev_timestamp,

timestampdiff(second, gt.log_timestamp, ru.rev_timestamp) seconds_ec_to_rev,

case when timestampdiff(second, gt.log_timestamp, ru.rev_timestamp) > 0 then True else False 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

left join grant_timestamp gt on gt.log_actor = ar.actor_id

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 '2020-10-07' and '2021-10-06'

and date(ru.rev_timestamp) between '2023-10-07' and '2024-10-06'

and ar.actor_name in ('Ainty Painty','Pave Paws','24.130.244.5','Canterbury Tail','Zero0000')

-- group by 2

order by 5

-- limit 100