Translatators of board seat election pages per language and volume

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 AND
	-- Must use the database format for titles, e.g. use underscores instead of spaces
	-- % means any string
	(page_title LIKE 'Affiliate-selected_Board_seats/2016/%' OR page_title LIKE 'Affiliate-selected_Board_seats_2016_election/%' ) AND
	user_id > 0 AND
	user_id NOT IN (
		SELECT
			user_id
		FROM
			page
		LEFT JOIN
			revision ON (page_id = rev_page)
		LEFT JOIN
			user ON (rev_user = user_id)
		WHERE
			page_namespace = 1198 AND
			rev_timestamp > (now() - INTERVAL 180 DAY + 0) AND
			user_id > 0
		GROUP BY user_id
	)
GROUP BY
	user, language
HAVING
    no_translations > 5
ORDER BY
	language, no_translations DESC;

SQL

Checking query status...