This query is marked as a draft This query has been published by Cryptic.

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.

Checking query status...