Test 3 - Quarry

SQL

-- Use the enwiki_p database
USE enwiki_p;

-- Find the top 100 categories available on most languages/wikipedias
WITH TopCategories AS (
  SELECT
    cl_to AS category_title,
    COUNT(DISTINCT ll_lang) AS language_count
  FROM
    categorylinks
  LEFT JOIN
    langlinks ON ll_title = cl_to
  WHERE
    ll_lang IS NOT NULL
  GROUP BY
    cl_to
  HAVING
    language_count > 1
  ORDER BY
    language_count DESC
  LIMIT 100
)

-- Find categories not available in ckbwiki and not members of "Category:Hidden categories"
SELECT
  category_title
FROM
  TopCategories
WHERE
  category_title NOT IN (SELECT cl_to FROM categorylinks WHERE cl_from = (SELECT page_id FROM page WHERE page_title = 'Hidden_categories'))
  AND
  category_title NOT IN (SELECT cl_to FROM categorylinks WHERE cl_from = (SELECT page_id FROM page WHERE page_title = 'Hidden_categories' AND page_namespace = 14))
  AND
  category_title NOT IN (SELECT ll_title FROM langlinks WHERE ll_lang = 'ckb')
LIMIT 100;
SQL

Checking query status...
