Orphan subpages of portals

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
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 "^[^/]+/[^/]+$"
-- {{Random portal component}} embeds sub-subpages but only links the subpage
GROUP BY page_title
-- Otherwise a first-level subpage without any transclusion is suspect:
HAVING transclusions = 0;
/* WHERE SUBSTRING_INDEX(page_title, "/", 1) IN (
  SELECT page_title
  FROM page
  JOIN templatelinks
  ON tl_from = page_id
  AND page_namespace = 100
  AND tl_title LIKE "Transclude_%_excerpt" 
  AND LOCATE("/", page_title) = 0 ) */
  ;
SQL

Checking query status...
