EC coverage - PIA topic area

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

/*
- objective - get an estimate of the extendedconfirmed protection coverage for the PIA topic area
- uses Zero's expanded definition for the approximation of the topic area
- try new way to select page titles in topic area (comment out talk pages for this particular query) 
  - appears to be much faster standalone, but may not be significantly different when used as part of 
    larger queries - hard to tell due to the server's wildly varying performance and plan caching
- there are probably other ways to do this that may produce different results
  - look at the logs
  - or join topic area pages to Category:Wikipedia extended-confirmed-protected pages - the catch being that you need to 
    traverse the category graph because there are subcats, so would need to use hierarchical query 
  - or look for pp templates - the catch being that there is diversity
*/
with pia_titles1 as (
select
p.page_id, p.page_title, p.page_namespace
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 p.page_namespace = 1 and p.page_is_redirect = 0
),
pia_titles2 as (
select
p.page_id, p.page_title, p.page_namespace
from 
page p
join categorylinks cl_a on p.page_id = cl_a.cl_from and cl_a.cl_to = "WikiProject_Israel_articles"
join categorylinks cl_b on p.page_id = cl_b.cl_from and cl_b.cl_to = "WikiProject_Palestine_articles"
where p.page_namespace = 1 and p.page_is_redirect = 0
union
select
p.page_id, p.page_title, p.page_namespace
from
page p
join categorylinks cl_a on page_id = cl_a.cl_from and cl_a.cl_to = "WikiProject_Israel_articles"
join categorylinks cl_b on page_id = cl_b.cl_from and cl_b.cl_to = "WikiProject_Lebanon_articles"
where p.page_namespace = 1 and p.page_is_redirect = 0
union
select
p.page_id, p.page_title, p.page_namespace
from
page p
join categorylinks cl_a on page_id = cl_a.cl_from and cl_a.cl_to = "WikiProject_Israel_articles"
join categorylinks cl_b on page_id = cl_b.cl_from and cl_b.cl_to = "WikiProject_Syria_articles"
where p.page_namespace = 1 and p.page_is_redirect = 0
union
select
p.page_id, p.page_title, p.page_namespace
from
page p
join categorylinks cl_a on page_id = cl_a.cl_from and cl_a.cl_to = "WikiProject_Israel_Palestine_Collaboration_articles"
where p.page_namespace = 1 and p.page_is_redirect = 0
),
pia_talk as (
select * from pia_titles1
union
select * from pia_titles2
),
pia as (
select * from pia_talk
union
select p.page_id, p.page_title, p.page_namespace from page p
join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 0 and p.page_is_redirect = 0 -- article
union
select p.page_id, p.page_title, p.page_namespace from page p 
join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 10 and p.page_is_redirect = 0 -- template
-- union
-- select p.page_id, p.page_title, p.page_namespace from page p 
-- join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 11 and p.page_is_redirect = 0
union
select p.page_id, p.page_title, p.page_namespace from page p 
join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 14 and p.page_is_redirect = 0 -- category
-- union
-- select p.page_id, p.page_title, p.page_namespace from page p 
-- n pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 15 and p.page_is_redirect = 0
union
select p.page_id, p.page_title, p.page_namespace from page p 
join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 100 and p.page_is_redirect = 0 -- portal
-- union
-- select p.page_id, p.page_title, p.page_namespace from page p 
-- join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 101 and p.page_is_redirect = 0
union
select p.page_id, p.page_title, p.page_namespace from page p 
join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 118 and p.page_is_redirect = 0 -- draft
-- union
-- select p.page_id, p.page_title, p.page_namespace from page p 
-- join pia_talk on p.page_title = pia_talk.page_title and p.page_namespace = 119 and p.page_is_redirect = 0
),
ec_protected as (
select p.page_id, convert(replace(p.page_title,'_',' ') using utf8mb4) page_title, p.page_namespace,
	case
	when p.page_namespace = 0 then '(Main/Article)'
	when p.page_namespace = 1 then 'Talk'
	when p.page_namespace = 2 then 'User'
	when p.page_namespace = 3 then 'User talk'
	when p.page_namespace = 4 then 'Wikipedia'
	when p.page_namespace = 5 then 'Wikipedia talk'
	when p.page_namespace = 6 then 'File'
	when p.page_namespace = 7 then 'File talk'
	when p.page_namespace = 8 then 'MediaWiki'
	when p.page_namespace = 9 then 'MediaWiki talk'
	when p.page_namespace = 10 then 'Template'
	when p.page_namespace = 11 then 'Template talk'
	when p.page_namespace = 12 then 'Help'
	when p.page_namespace = 13 then 'Help talk'
	when p.page_namespace = 14 then 'Category'
	when p.page_namespace = 15 then 'Category talk'
	when p.page_namespace = 100 then 'Portal'
	when p.page_namespace = 101 then 'Portal talk'
	when p.page_namespace = 118 then 'Draft'
	when p.page_namespace = 119 then 'Draft talk'
	when p.page_namespace = 710 then 'TimedText'
	when p.page_namespace = 711 then 'TimedText talk'
	when p.page_namespace = 828 then 'Module'
	when p.page_namespace = 829 then 'Module talk'
	else null
	end as namespace_desc,
case when pr.pr_level is null then 0 else 1 end ec_protected
from 
pia p
left join page_restrictions pr on p.page_id = pr.pr_page and pr.pr_level = 'extendedconfirmed' and pr.pr_type = 'edit'
where mod(p.page_namespace,2) = 0 -- exclude talk pages
)
select * from ec_protected 
union
select '',' ','','',
concat(
round(100*sum(ec_protected)/count(*),1),
'% (',
sum(ec_protected),
' protected out of ',
count(*),
' pages)'
) from ec_protected
order by page_title, page_namespace
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...
