Wikipedia:WikiProject Chemicals/Lists of pages/Chembox templates transclusion count

DROP TABLE IF EXISTS temp_chemtemplates;
 
CREATE TABLE temp_chemtemplates AS
  SELECT MIN(t.page_namespace) 'ctp_namespace', MIN(t.page_title) 'ctp_title', 0 'ctp_count'
  FROM enwiki_p.categorylinks
  INNER JOIN enwiki_p.page t ON t.page_id = cl_from
  WHERE cl_to = 'Chembox_templates'
  AND t.page_namespace = 10
  GROUP BY t.page_id;

UPDATE temp_chemtemplates
SET ctp_count = (
  SELECT count(*)
  FROM enwiki_p.templatelinks
  INNER JOIN enwiki_p.page ON page_id = tl_from
  WHERE tl_namespace = ctp_namespace
  AND   tl_title = ctp_title
  AND   page_namespace = 0 );
  
SELECT ctp_title, ctp_count
FROM temp_chemtemplates;

DROP TABLE IF EXISTS temp_catlist;
 
CREATE TABLE temp_catlist (
      ctl_id INT(8) UNSIGNED NOT NULL,
      ctl_name VARCHAR(255) BINARY NOT NULL,
      ctl_nestlevel INT(8) UNSIGNED NOT NULL,
 
      PRIMARY KEY (ctl_id)
);
 
TRUNCATE TABLE temp_catlist;
 
-- Initial category
INSERT INTO temp_catlist
SELECT page_id, page_title, 1
FROM enwiki_p.page
WHERE page_namespace = 14
AND   page_title = 'Chembox_templates';
 
-- Subcategories
INSERT INTO temp_catlist
SELECT page_id, page_title, ctl_nestlevel + 1
FROM enwiki_p.categorylinks
INNER JOIN temp_catlist ON cl_to = ctl_name
INNER JOIN enwiki_p.page ON page_id = cl_from
WHERE page_namespace = 14;
 

DROP TABLE IF EXISTS temp_chemtemplates;
 
CREATE TABLE temp_chemtemplates AS
  SELECT MIN(page_namespace) 'ctp_namespace', MIN(page_title) 'ctp_title', 0 'ctp_count'
  FROM temp_catlist
  INNER JOIN enwiki_p.categorylinks ON cl_to = ctl_name 
  INNER JOIN enwiki_p.page ON page_id = cl_from
  WHERE page_namespace = 10
  GROUP BY page_id;

 
UPDATE temp_chemtemplates
SET ctp_count = (
  SELECT COUNT(*)
  FROM enwiki_p.templatelinks
  INNER JOIN enwiki_p.page ON page_id = tl_from
  WHERE tl_namespace = ctp_namespace
  AND   tl_title = ctp_title );
 
SELECT ctp_title, ctp_count
FROM temp_chemtemplates;