SQL
x
SET @basecat='Wormholes'; -- base category, with underscores instead of spaces
SET max_recursive_iterations=3; -- maximum depth in tree. 0 is just @basecat; 1 is @basecat and its children; 2 is those and their children.
-- Here, 3 is enough for the full tree, with the deepest leaf at Wormholes > Wormholes in art > Wikiwormhole > SVG Wikiwormhole logos
-- (though all files from that category are also in Category:Wikiwormhole, so it doesn't show up in the results here)
WITH pivot (p_title, p_path, p_count, p_wiki) AS
(
WITH files_in_tree (fit_title, fit_depth, fit_path) AS
(
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 page_title,
MIN(depth),
MIN(catpath) OVER (PARTITION BY page_title ORDER BY depth ASC)
FROM page
JOIN categorylinks ON cl_from = page_id
JOIN deepcat ON cl_to = subcat
WHERE page_namespace = 6
GROUP BY page_id
)
SELECT fit_title, fit_path, COUNT(gil_page) as perwiki_count, gil_wiki
FROM globalimagelinks
JOIN files_in_tree ON fit_title = gil_to
WHERE gil_page_namespace_id = 0
GROUP BY fit_title, gil_wiki
)
SELECT REPLACE(p_title, '_', ' ') AS title,
p_path AS 'category path',
GROUP_CONCAT(CONCAT(p_count, '@', p_wiki) SEPARATOR ', ') AS 'wiki usage'
FROM pivot
GROUP BY p_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.