Composers sans templates

Direct subcategories of [[:Category:Compositions by composer]] whose titles start with "Compositions by ", have at least two members, and there's no existing template named exactly [[Template:{whatever comes after the "Compositions by "}]].

For [[WP:RAQ#Composition categories without templates]] circa 28 July 2024.

SQL

WITH subcats(sc_title, sc_name) AS
(
  SELECT page_title, REGEXP_REPLACE(page_title, '^Compositions_by_', '')
  FROM categorylinks
  JOIN page ON page_id = cl_from
  WHERE cl_to = 'Compositions_by_composer'
    AND page_namespace = 14
    AND page_title LIKE 'Compositions\_by\_%'
)
SELECT CONCAT('[[Template:', sc_name, ']]') AS template,
       CONCAT('[[:Category:', sc_title, ']]') AS subcategory,
       COUNT(*)
FROM subcats
JOIN categorylinks ON cl_to = sc_title
LEFT JOIN page ON page_namespace = 10 AND page_title = sc_name
WHERE page_id IS NULL
GROUP BY sc_title
HAVING COUNT(*) >= 2;

SQL

Checking query status...