global file counts (ns 0) in cat tree

Global file usage counts of all files in a Commons category tree.  Counts only usage in wikis' main namespaces.

Uses Category:Wormhole as a demonstration, for sanity's sake: this tree currently has a maximum depth of 3, over 8 total categories and 90 total files, of which 44 appear in at least one wiki's main namespace.  (The originally-requested tree has about a million and a half files at maximum depth 7.)

For [[w:en:WP:RAQ#Global file links breakdown for category]] circa 13 Sep 2023.

SQL

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;

SQL

Checking query status...