Test 3 - Quarry

SQL

-- First, let's create a temporary table to store the categories that are members of "Category:Hidden_categories" on enwiki.
CREATE TEMPORARY TABLE HiddenCategories AS
SELECT DISTINCT ll_from
FROM langlinks
WHERE ll_lang = 'en'
  AND ll_title = 'Category:Hidden_categories';

-- 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 DISTINCT
  lc.ll_title AS EnglishCategory,
  COUNT(lc.ll_lang) AS LanguageCount
FROM langlinks AS lc
WHERE lc.ll_from IN (
  SELECT DISTINCT ll_from
  FROM langlinks
  WHERE ll_lang = 'en'
    AND ll_title NOT LIKE 'Category:%Hidden_categories%'
)  -- Exclude "Category:Hidden_categories" on enwiki
  AND lc.ll_lang != 'ckbwiki'  -- Exclude ckbwiki
GROUP BY lc.ll_title
HAVING LanguageCount > 1
ORDER BY LanguageCount DESC
LIMIT 100;
SQL

Checking query status...
