Templates with link and redlink counts

SQL

SELECT Pt.page_title, COUNT(*) links, SUM(CASE WHEN Pa.page_title IS NULL THEN 1 ELSE 0 END) reds,
 GROUP_CONCAT(CASE WHEN Pa.page_title IS NULL THEN CONCAT("|",pl_title) ELSE NULL END) AS redlinks,
 (SELECT GROUP_CONCAT(cl_to) FROM categorylinks WHERE cl_from = pl_from AND cl_type='page') AS categories
FROM pagelinks
/* JOIN page Pt ON Pt.page_id = pl_from */
LEFT JOIN page Pa ON Pa.page_title = pl_title AND Pa.page_namespace = 0
JOIN page Pt ON Pt.page_id = pl_from AND Pt.page_title NOT REGEXP "/(doc|sandbox|testcases)\\b"
WHERE pl_from_namespace = 10 AND pl_namespace = 0 /* Template→Article links only */
GROUP BY Pt.page_title WITH ROLLUP
HAVING links >= 100 AND reds BETWEEN 1 AND 5

AخA
 
SELECT Pt.page_title, COUNT(*) links, SUM(CASE WHEN Pa.page_title IS NULL THEN 1 ELSE 0 END) reds, GROUP_CONCAT(CASE WHEN Pa.page_title IS NULL THEN CONCAT("|",pl_title) ELSE NULL END) AS redlinks, (SELECT GROUP_CONCAT(cl_to) FROM categorylinks WHERE cl_from = pl_from AND cl_type='page') AS categoriesFROM pagelinks/* JOIN page Pt ON Pt.page_id = pl_from */LEFT JOIN page Pa ON Pa.page_title = pl_title AND Pa.page_namespace = 0JOIN page Pt ON Pt.page_id = pl_from AND Pt.page_title NOT REGEXP "/(doc|sandbox|testcases)\\b"WHERE pl_from_namespace = 10 AND pl_namespace = 0 /* Template→Article links only */GROUP BY Pt.page_title WITH ROLLUPHAVING links >= 100 AND reds BETWEEN 1 AND 5

SQL

Checking query status...