Wikidata - pages not on enwiki but in at least 25 others

Fork of Wikidata - pages on simplewiki but not enwiki by BilledMammal

This query is marked as a draft This query has been published by BilledMammal.

SQL

SELECT CONCAT("Q",ips_item_id) as Item, CASE WHEN wbx_text IS NOT NULL THEN wbx_text ELSE 'UNKNOWN' END as Name, Number of articles
FROM (ips_item_id, COUNT(ips_site_id) as number_of_wikis
	FROM wb_items_per_site
	WHERE ips_item_id NOT IN (
		SELECT ips_item_id
		FROM wb_items_per_site
		WHERE ips_site_id = 'enwiki')
	AND ips_site_page NOT LIKE '%:%'
	GROUP BY ips_item_id
	HAVING number_of_wikis > 25
	ORDER BY number_of_wikis DESC) items
LEFT JOIN wbt_item_terms ON wbit_item_id = item_id
LEFT JOIN wbt_term_in_lang ON wbit_term_in_lang_id = wbtl_id AND wbtl_type_id = 1
LEFT JOIN wbt_text_in_lang ON wbtl_text_in_lang_id = wbxl_id AND wbxl_language = 'en'
LEFT JOIN wbt_text ON wbxl_text_id = wbx_id

SQL

Checking query status...