Articles w/title matching regex in cat tree, w/path

Non-redirect mainspace pages containing a Greek letter name in Latin script (case-insensitive, distinct word) that are in a subcategory of [[Category:Chemistry]] to a maximum depth of 7.

For [[WP:RAQ#Spelled out Latin characters in Chemistry articles]] circa 9 July 2023.

SQL

SET @basecat='Mathematics';
SET max_recursive_iterations=7;

WITH RECURSIVE deepcat (subcat, catpath, depth) AS
(
  SELECT REPLACE(@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,
       MIN(depth),
       MIN(catpath) OVER (PARTITION BY page_title ORDER BY depth ASC) AS 'category path'
FROM page
JOIN categorylinks ON cl_from = page_id
JOIN deepcat ON cl_to = subcat
WHERE page_namespace = 0
  AND page_is_redirect = 0
  AND LOWER(CONVERT(page_title USING 'utf8')) REGEXP '_dog'
GROUP BY page_title
ORDER BY MIN(depth) ASC;

SQL

Checking query status...