Untitled query #88106

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

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

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
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
),
bots as (
  select ug_user
 	from user_groups ug
 	where ug_group = 'bot'
),
grant_timestamp as (
select log_actor, max(log_timestamp) log_timestamp
from logging_userindex
where log_type = 'rights' 
and log_params rlike 'extendedconfirmed'
group by 1
),
pia_rev as (
select
page_namespace,
rev_id,
r.rev_actor,
MAX(CASE WHEN ct_tag_id = @reverted THEN 1 ELSE 0 END) AS pia_reverted,
MAX(CASE WHEN ct_tag_id in (@undo,@rollback,@manual) THEN 1 ELSE 0 END) AS pia_revert,
MIN(CASE WHEN (a.actor_user is NULL OR u.user_editcount < 500
               OR DATEDIFF(DATE(r.rev_timestamp),DATE(u.user_registration)) < 30
               OR gt.log_timestamp > r.rev_timestamp
              )
     then 0 else 1 end) AS pia_ec
from pia
left join revision_userindex as r on r.rev_page = pia.page_id
left join change_tag as ct ON ct.ct_rev_id = r.rev_id
left join actor as a on a.actor_id = r.rev_actor
left join user as u on u.user_id = a.actor_user
left join grant_timestamp as gt on gt.log_actor = r.rev_actor
where
  date(r.rev_timestamp) BETWEEN '2020-10-07' AND '2021-10-06'
  and
  a.actor_user in (select * from bots) is not true
  group by page_namespace,rev_id
),
pia_final as (
select
  page_namespace,
  pia_ec,
  count(distinct rev_actor) AS actors,
  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_rev
 group by page_namespace,pia_ec
)
select
 page_namespace as namespace,
 pia_ec as EC,
 actors,
 rev_count as edits,
 reverts,
 CONCAT(FORMAT(100.0 * reverts/rev_count,2),'%') as revertpercent,
 reverted,
 CONCAT(FORMAT(100.0 * reverted/rev_count,2),'%') as revertedpercent,
 reverted_reverts,
 CONCAT(FORMAT(100.0 * reverted_reverts/rev_count,2),'%') as reverted_revert_percent
from
pia_final
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...
