Test 3 - Quarry

SQL

USE wikidatawiki_p;

WITH CategoryCounts AS (
  SELECT
    cl_to AS category_title,
    COUNT(DISTINCT cl_lang) AS language_count
  FROM categorylinks
  WHERE
    cl_to NOT LIKE 'Hidden_categories%'
    AND cl_to NOT IN (
      SELECT ll_title
      FROM langlinks
      WHERE ll_lang = 'en'
      AND ll_title = 'Hidden_categories'
    )
  GROUP BY cl_to
),
CategoryExclusion AS (
  SELECT
    ll_title AS category_title
  FROM langlinks
  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...
