Test 3 - Quarry

SQL

-- Find the top 100 categories available in most languages/wikipedias but not in ckbwiki
USE wikidatawiki_p;

-- Create a temporary table to store category data
CREATE TEMPORARY TABLE top_categories AS
SELECT
  ll_title AS category_title,
  COUNT(DISTINCT ll_lang) AS language_count
FROM langlinks
WHERE ll_title LIKE 'Category:%'
GROUP BY ll_title
HAVING language_count >= 2  -- Adjust the threshold as needed
ORDER BY language_count DESC
LIMIT 100;

-- Fetch the top 100 categories not available in ckbwiki
SELECT
  tc.category_title AS english_category_title
FROM top_categories tc
LEFT JOIN ckbwiki.langlinks ckb ON tc.category_title = ckb.ll_title AND ckb.ll_lang = 'en'
WHERE ckb.ll_title IS NULL;
SQL

Checking query status...
