non-redir articles in cat tree, max length, list els

SQL

SET @basecat='Gridiron_football_players';
SET max_recursive_iterations=8; -- current maximum depth for this tree; 2777 categories, 49831 pages

WITH RECURSIVE deepcat (subcat, catpath, depth) AS
(
  SELECT @basecat, REPLACE(@basecat, '_', ' '), 0

  UNION

  SELECT page_title, CONCAT(catpath, ' > ', REPLACE(page_title, '_', ' ')), depth + 1
  FROM categorylinks
  JOIN page ON page_id = cl_from AND page_namespace = 14
  JOIN deepcat ON cl_to = subcat
)
SELECT DISTINCT REPLACE(page_title, '_', ' ') AS title,
       page_len,
       MIN(depth),
       MIN(catpath) OVER (PARTITION BY page_title ORDER BY depth ASC) AS 'category path',
       GROUP_CONCAT(DISTINCT els.el_to SEPARATOR ' ') AS 'external links'
FROM page
JOIN categorylinks ON cl_from = page_id
JOIN deepcat ON cl_to = subcat
LEFT JOIN externallinks AS els ON els.el_from = page_id
WHERE page_namespace = 0
  AND page_is_redirect = 0
  AND page_len <= 1500
GROUP BY page_id
ORDER BY MIN(depth) ASC;
SQL

Checking query status...
