Pages by last edit time in cat tree 1

Non-redirect mainspace pages in the category tree rooted at [[:Category:Food and drink]] by order of the timestamp of their latest edit, to a maximum depth of 3 subcats (for example,
[[:Category:Food and drink]] > [[:Category:Cuisine]] > [[:Category:Confectionery]] > [[:Category:Ice cream]] > [[Ice Milk]]).

For [[WP:RAQ#Dusty articles within all subcategories of a parent category]] circa 17 November 2024.

SQL

SET @basecat = 'Food and drink';
SET max_recursive_iterations = 3;

SET @basecat = REPLACE(@basecat, ' ', '_');
WITH RECURSIVE deepcat (subcat) AS
(
  SELECT @basecat

  UNION DISTINCT

  SELECT page_title
  FROM categorylinks
  JOIN page ON page_id = cl_from
  JOIN deepcat ON cl_to = subcat
  WHERE page_namespace = 14
)
SELECT DISTINCT REPLACE(page_title, '_', ' ') AS title,
                CAST(rev_timestamp AS DATETIME) AS 'last edit timestamp'
FROM page
JOIN categorylinks ON cl_from = page_id
JOIN deepcat ON cl_to = subcat
JOIN revision ON rev_id = page_latest
WHERE page_namespace = 0
  AND page_is_redirect = 0
ORDER BY rev_timestamp ASC;

SQL

Checking query status...