Untitled query #86055

SQL

WITH template_links AS (
  SELECT 
    tl.tl_from,
    tl.tl_namespace,
    COUNT(DISTINCT pl.pl_title) as total_links,
    SUM(CASE WHEN p2.page_id IS NULL THEN 1 ELSE 0 END) as red_links
  FROM templatelinks tl
  JOIN page p ON tl.tl_from = p.page_id
  LEFT JOIN pagelinks pl ON pl.pl_from = tl.tl_from
  LEFT JOIN page p2 ON pl.pl_namespace = p2.page_namespace AND pl.pl_title = p2.page_title
  WHERE tl.tl_namespace = 10  -- Пространство имён шаблонов
  GROUP BY tl.tl_from, tl.tl_namespace
)
SELECT 
  p.page_title as template_name,
  tl.total_links,
  tl.red_links,
  ROUND(tl.red_links * 100.0 / tl.total_links, 2) as red_links_percentage
FROM template_links tl
JOIN page p ON tl.tl_from = p.page_id
WHERE tl.red_links = tl.total_links
AND tl.total_links > 0
ORDER BY tl.total_links DESC
LIMIT 100;
SQL

Checking query status...
