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 DISTINCT(CONCAT("Q",ips_item_id)) as Item, COALESCE(wbx_text, 'UNKNOWN') as Name, number_of_wikis as 'Number of articles'
FROM (
	SELECT 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) items
LEFT JOIN wbt_item_terms ON wbit_item_id = ips_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
ORDER BY number_of_wikis DESC

SQL

Checking query status...