ruwiki navboxes with redlinks

SQL

WITH RECURSIVE nav_categories AS (
  -- Базовая категория и её подкатегории
  SELECT page_id, page_title 
  FROM categorylinks cl
  JOIN page p ON cl.cl_from = p.page_id
  WHERE cl.cl_to = 'Навигационные_шаблоны'
  AND p.page_namespace = 14  -- Пространство имён категорий
  
  UNION ALL
  
  SELECT p.page_id, p.page_title
  FROM categorylinks cl
  JOIN page p ON cl.cl_from = p.page_id
  JOIN nav_categories nc ON cl.cl_to = nc.page_title
  WHERE cl.cl_type = 'subcat'
  AND p.page_namespace = 14
),
template_links AS (
  -- Получаем все шаблоны из этих категорий и их ссылки
  SELECT 
    t.page_id as template_id,
    t.page_title as template_name,
    COUNT(pl.pl_target_id) as total_links,
    SUM(CASE WHEN p.page_id IS NULL THEN 1 ELSE 0 END) as red_links
  FROM categorylinks cl
  JOIN page t ON cl.cl_from = t.page_id
  JOIN nav_categories nc ON cl.cl_to = nc.page_title
  LEFT JOIN pagelinks pl ON pl.pl_from = t.page_id
  LEFT JOIN page p ON pl.pl_target_id = p.page_id
  WHERE cl.cl_type = 'page'
  AND t.page_namespace = 10  -- Пространство имён шаблонов
  GROUP BY t.page_id, t.page_title
)
SELECT 
    template_name,
    total_links,
    red_links,
    ROUND(red_links * 100.0 / total_links, 2) as red_links_percentage
FROM template_links
WHERE total_links > 0 
AND red_links = total_links
ORDER BY total_links DESC
LIMIT 50;
SQL

Checking query status...
