Test 3 - Quarry

SQL

-- USE the appropriate database
USE wikidatawiki_p;

-- Find categories that are available in most languages but not in ckbwiki and count site links
SELECT
  enwiki.ll_title AS category_title,
  COUNT(DISTINCT sitelinks.ll_title) AS site_link_count
FROM
  (
    SELECT
      DISTINCT ll_title
    FROM
      langlinks
    WHERE
      -- Exclude categories from ckbwiki
      ll_lang != 'ckb' AND
      -- Filter by namespace (Category)
      ll_title LIKE 'Category:%'
  ) AS other_languages
  LEFT JOIN langlinks AS enwiki ON enwiki.ll_title = other_languages.ll_title AND enwiki.ll_lang = 'en'
  LEFT JOIN pagelinks AS sitelinks ON sitelinks.pl_title = enwiki.ll_title
WHERE
  enwiki.ll_title IS NOT NULL
GROUP BY enwiki.ll_title
HAVING COUNT(DISTINCT enwiki.ll_lang) > 5 -- Adjust the threshold for the minimum number of languages as needed
ORDER BY site_link_count DESC
LIMIT 100;
SQL

Checking query status...
