Untitled query #87042

Based on https://meta.wikimedia.org/wiki/Research_talk:Quarry#c-Prototyperspective-20240924234200-Query_request

SQL

SELECT 
  REPLACE(page_title,'_',' ') as category_name,
  cat_pages as cat_size,
  if (cat_pages = 0, "-", if (cat_pages = cat_subcats, "S", if(cat_pages = cat_files, "F", "S+F"))) as cat_content,
  page_id  
FROM category LEFT JOIN
{
SELECT DISTINCT 
  page_title, page_id  
FROM page AS P1, categorylinks
WHERE cl_to = page_title
AND page_namespace = 14 
AND page_is_redirect = 0
AND NOT EXISTS (
  SELECT 1 FROM categorylinks
  JOIN page AS P2 ON P2.page_namespace = 14 AND cl_to = P2.page_title
  WHERE cl_from = P1.page_id
) LIMIT 1
} as tmp ON cat_title = tmp.page_title

SQL

Checking query status...