Most wanted articles: ns0→ns0 redlinks only ; suppress templates

Fork of Most wanted articles: ns0→ns0 redlinks only by Certes

This query is marked as a draft This query has been published by Andrew Gray.

SQL

# derived from https://quarry.wmcloud.org/query/71997
# suppressing any with links from templates

# this does not work 

SELECT pl_title Target, SUM(page_title IS NULL) ArticleLinkCount
FROM pagelinks
LEFT JOIN page ON page_namespace=0 AND page_title=pl_title
WHERE pl_from_namespace=0 AND pl_namespace=0 AND pl_title < "B" 
GROUP BY Target
#HAVING LinkCount>500 
EXCEPT
SELECT pl_title Target, SUM(page_title IS NULL) TemplateLinkCount
FROM pagelinks
LEFT JOIN page ON page_namespace=0 AND page_title=pl_title
WHERE pl_from_namespace=10 AND pl_namespace=0 AND pl_title < "B" 
GROUP BY Target
#HAVING LinkCount>=1 # linked from one or more templates
ORDER BY LinkCount DESC

SQL

Checking query status...