Wikidata:Some items to be merged in Wikidata

Fork of Translators at meta with language, number of translations by Elitre (WMF)

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

For a given prefix of translatable pages, take all translators for the latest versions of translation units (otherwise someone fixing their own typos would look a prolific translator) and order them by language and translation volume.

SQL

USE wikidatawiki_p;
SELECT CONCAT('* ', wbA.ips_site_page, ' - [[Q', wbA.ips_item_id, ']] & [[Q', GROUP_CONCAT(DISTINCT wbC.ips_item_id ORDER BY wbC.ips_item_id ASC SEPARATOR ']], [[Q'), ']]')
	FROM wb_items_per_site AS wbA
	LEFT JOIN wb_items_per_site AS wbB
		ON (wbA.ips_item_id = wbB.ips_item_id AND NOT wbA.ips_site_id = wbB.ips_site_id)
	JOIN wb_items_per_site AS wbC
		ON (wbA.ips_site_page = wbC.ips_site_page AND NOT wbA.ips_site_id = wbC.ips_site_id)
WHERE wbA.ips_site_id = 'cswiki'
AND wbB.ips_site_id IS NULL
AND NOT EXISTS (
	SELECT * FROM wb_items_per_site AS wbD
	WHERE wbD.ips_item_id = wbC.ips_item_id
	AND wbD.ips_site_id = 'cswiki'
)
GROUP BY wbA.ips_site_page
ORDER BY MAX(wbC.ips_item_id) DESC;

SQL

Checking query status...