SQL
x
SET @parentcat = 'Speedy deletion';
WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4,
'Wikipedia:'), (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'),
(11, 'Template talk:'), (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101,
'Portal talk:'), (118, 'Draft:'), (119, 'Draft talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'), (828, 'Module:'), (829,
'Module talk:'), (2300, 'Gadget:'), (2301, 'Gadget talk:'), (2302, 'Gadget definition:'), (2303, 'Gadget definition talk:'))
SELECT CONCAT(COALESCE(ns_s, CONCAT('{{ns:', log_namespace, '}}:')), REPLACE(log_title, '_', ' ')) AS title,
GROUP_CONCAT(DISTINCT REPLACE(zc.cl_to, '_', ' ') SEPARATOR ' | ') AS categories
FROM logging_logindex
JOIN categorylinks AS zc ON zc.cl_from = log_page
JOIN archive ON ar_namespace = log_namespace AND ar_title = log_title AND ar_page_id = log_page -- this filters out redirects left by moves
JOIN page ON page_namespace = 14 AND page_title = zc.cl_to
JOIN categorylinks AS sc ON sc.cl_from = page_id
LEFT JOIN ns ON ns_n = log_namespace
WHERE sc.cl_to = REPLACE(@parentcat, ' ', '_')
AND NOT EXISTS (SELECT 1 FROM page WHERE page_id = log_page)
GROUP BY log_namespace, log_title;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.