ruwiki navboxes with redlinks

SQL

WITH RECURSIVE nav_categories AS (
  -- Базовая категория
  SELECT cl_to, cl_from 
  FROM categorylinks 
  WHERE cl_to = 'Навигационные_шаблоны'
  
  UNION ALL
  
  -- Рекурсивно получаем все подкатегории
  SELECT cl.cl_to, cl.cl_from
  FROM categorylinks cl
  JOIN nav_categories nc ON cl.cl_to = nc.cl_from
  WHERE cl_type = 'subcat'
),
template_pages AS (
  -- Получаем все шаблоны из навигационных категорий
  SELECT DISTINCT cl.cl_from as page_id
  FROM categorylinks cl
  JOIN nav_categories nc ON cl.cl_to = nc.cl_to
  WHERE cl.cl_type = 'page'
)
SELECT 
    p.page_title as template_name,
    COUNT(DISTINCT pl.pl_target_id) as total_links,
    SUM(CASE WHEN p2.page_id IS NULL THEN 1 ELSE 0 END) as red_links
FROM template_pages tp
JOIN page p ON tp.page_id = p.page_id
LEFT JOIN pagelinks pl ON pl.pl_from = tp.page_id
LEFT JOIN page p2 ON p2.page_id = pl.pl_target_id
GROUP BY p.page_title
HAVING total_links > 0 
  AND total_links <= 100  -- Ограничиваем размер для производительности
  AND red_links = total_links
ORDER BY total_links DESC
LIMIT 50;
SQL

Checking query status...
