Test 3 - Quarry

SQL

USE enwiki_p;

-- Find the categories available on most languages/wikipedias
WITH MostAvailableCategories AS (
  SELECT
    ll_title AS category_title,
    COUNT(DISTINCT ll_lang) AS num_languages
  FROM langlinks
  WHERE
    ll_title LIKE 'Category:%'  -- Only consider category pages
  GROUP BY ll_title
  HAVING num_languages >= (SELECT COUNT(DISTINCT ll_lang) FROM langlinks WHERE ll_lang = 'ckbwiki')
  ORDER BY num_languages DESC
  LIMIT 100
)

-- Find categories that are not available in ckbwiki
SELECT
  mac.category_title
FROM MostAvailableCategories mac
LEFT JOIN langlinks ckb ON mac.category_title = ckb.ll_title AND ckb.ll_lang = 'ckbwiki'
WHERE ckb.ll_title IS NULL;
SQL

Checking query status...
