Orphan subpages of portals

Looks for first-level subpages of portals which were left without incoming links or transclusions, probably after being superseded by newer templates.

SQL

USE enwiki_p;
SELECT page_title, count(tl_from) AS transclusions, COUNT(pl_from) AS links -- , COUNT(tl_from)
FROM page
LEFT JOIN templatelinks
ON page_title = tl_title
AND tl_namespace = 100
AND tl_from_namespace = 100
LEFT JOIN pagelinks
ON page_title = pl_title
AND pl_from_namespace = 100
AND pl_namespace = 100
WHERE page_namespace = 100
-- Deeper levels of subpages may be transcluded only in rotation:
AND page_title RLIKE "^[^/]+/[^/]+$"
GROUP BY page_title
-- Otherwise a first-level subpage without any transclusion is suspect:
HAVING transclusions = 0
-- {{Random portal component}} and {{Transclude random subpage}} embed sub-subpages but only link the subpage
AND links = 0;

SQL

Checking query status...