Test 3 - Quarry

SQL

-- First, we need to get the page_id of "Category:Hidden_categories" on enwiki
-- and store it in a variable.
SET @hidden_category_page_id = (
  SELECT page_id
  FROM page
  WHERE page_title = "Hidden_categories"
  AND page_namespace = 14 -- Namespace for categories
  AND page_is_redirect = 0
  LIMIT 1
);

-- Now, let's fetch the top 100 English Wikipedia categories that are
-- available in most languages except ckbwiki and are not members of
-- "Category:Hidden_categories" on enwiki.
SELECT
  ll_title AS category_title,
  COUNT(DISTINCT ll_lang) AS language_count
FROM
  langlinks
WHERE
  ll_from IN (
    SELECT
      cl_from
    FROM
      categorylinks
    WHERE
      cl_to = "Hidden_categories"
      AND cl_type = "page"
  )
  AND ll_title NOT IN (
    SELECT
      page_title
    FROM
      categorylinks
    WHERE
      cl_from = @hidden_category_page_id
  )
  AND ll_lang <> "ckbwiki"
GROUP BY
  ll_title
HAVING
  language_count >= 100 -- Categories available in at least 100 languages
ORDER BY
  language_count DESC
LIMIT 100;
SQL

Checking query status...
