-- This query makes a list of all Wikipedia articles such that

-- 1. The talk page contains a template indicating ARBPIA relevance.

-- 2. The article is not protected at an EC or higher level.

-- Note that the database does not record whether the template has the

--    relatedcontent=yes or section=yes parameters, so if those articles

--    are not needed they have to be removed using non-database code.

​

WITH pia_titles AS (

SELECT

UNIQUE 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", "ARBPIA","Arab-Israeli_Arbitration_Enforcement")

AND page_namespace = 1 AND page_is_redirect = 0

)

​

SELECT REPLACE(pt.page_title,'_',' ') AS title

FROM pia_titles pt

JOIN page pg ON pg.page_title = pt.page_title

    AND pg.page_namespace = 0 AND pg.page_is_redirect = 0

LEFT JOIN page_restrictions AS r

    ON pg.page_id = r.pr_page AND r.pr_type = 'edit'

    AND r.pr_level IN ('extendedconfirmed','sysop')

   WHERE r.pr_page IS NULL

ORDER BY pt.page_title

​