Translators at meta with language, number of translations

Fork of Translatators of board seat election pages per language and volume by Abijeet Patro

This query is marked as a draft This query has been published by Elitre (WMF).

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 metawiki_p;

SELECT
	user_name as user,
	substring_index(page_title, '/', -1) AS language,
	count(*) AS no_translations,
    CONCAT('* {{target | page = User:', user_name, ' | site = meta.wikimedia.org}}')  AS url
FROM
	page
LEFT JOIN
	-- Take only the latest revision of each translation to simplify
	revision ON (page_id = rev_page AND page_latest = rev_id)
LEFT JOIN
	user ON (rev_user = user_id)
WHERE
	page_namespace = 1198 
GROUP BY
	user, language
HAVING
    no_translations > 5
ORDER BY
	language, no_translations DESC;

​x
 
use metawiki_p;​SELECT    user_name as user,    substring_index(page_title, '/', -1) AS language,    count(*) AS no_translations,    CONCAT('* {{target | page = User:', user_name, ' | site = meta.wikimedia.org}}')  AS urlFROM    pageLEFT JOIN    -- Take only the latest revision of each translation to simplify    revision ON (page_id = rev_page AND page_latest = rev_id)LEFT JOIN    user ON (rev_user = user_id)WHERE    page_namespace = 1198 GROUP BY    user, languageHAVING    no_translations > 5ORDER BY    language, no_translations DESC;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...