Test 3 - Quarry

SQL

-- Use the enwiki_p database
USE enwiki_p;

-- Get the top 100 categories that are available in most languages but not in ckbwiki
WITH CategoryCounts AS (
  SELECT
    cat.page_title AS category_title,
    COUNT(DISTINCT ll.ll_lang) AS language_count
  FROM categorylinks AS cat
  JOIN langlinks AS ll ON cat.cl_from = ll.ll_from
  WHERE NOT EXISTS (
    SELECT 1
    FROM categorylinks AS hidden_cat
    WHERE hidden_cat.cl_from = cat.cl_from
      AND hidden_cat.cl_to = 'Hidden_categories'
    )
  GROUP BY cat.page_title
  HAVING language_count >= (
    SELECT COUNT(DISTINCT ll_lang)
    FROM langlinks
    WHERE ll_from = (SELECT page_id FROM page WHERE page_title = 'Category:Hidden_categories')
  )
  ORDER BY language_count DESC
  LIMIT 100
)

-- Get the top 100 categories that are not available in ckbwiki
SELECT
  cc.category_title
FROM CategoryCounts AS cc
LEFT JOIN categorylinks AS ckb ON cc.category_title = ckb.cl_to
  AND ckb.cl_from = (SELECT page_id FROM page WHERE page_title = 'Category:Hidden_categories')
WHERE ckb.cl_to IS NULL
LIMIT 100;
SQL

Checking query status...
