Test 3 - Quarry

SQL

USE enwiki_p;

WITH CategoryCounts AS (
  SELECT
    page_title AS category_title,
    COUNT(DISTINCT ll_lang) AS language_count
  FROM page
  LEFT JOIN categorylinks ON cl_from = page_id
  LEFT JOIN langlinks ON ll_from = page_id
  WHERE
    cl_to NOT LIKE 'Hidden_categories%'
    AND ll_title IS NOT NULL
    AND ll_lang <> 'ckb'
  GROUP BY page_title
),
CategoryExclusion AS (
  SELECT
    page_title AS category_title
  FROM page
  LEFT JOIN langlinks ON page_id = ll_from
  WHERE
    ll_lang = 'en'
    AND ll_title = 'Hidden_categories'
),
CategoryCKB AS (
  SELECT DISTINCT
    cl_to AS category_title
  FROM categorylinks
  WHERE cl_lang = 'ckb'
),
TopCategories AS (
  SELECT
    cc.category_title
  FROM CategoryCounts cc
  LEFT JOIN CategoryExclusion ce ON cc.category_title = ce.category_title
  LEFT JOIN CategoryCKB ckb ON cc.category_title = ckb.category_title
  WHERE
    ce.category_title IS NULL
    AND ckb.category_title IS NULL
  ORDER BY cc.language_count DESC
  LIMIT 100
)

SELECT
  tc.category_title AS category
FROM TopCategories tc;
SQL

Checking query status...
