Top recent meta-wiki translators of specific languages

SQL

use metawiki_p;

SELECT
	actor_name as user,
	substring_index(page_title, '/', -1) AS language,
	count(*) AS no_translations,
    CONCAT("https://meta.wikimedia.org/wiki/Special:Contributions/", actor_name) AS Usercontribs
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
	actor_revision ON (rev_actor = actor_id)    
WHERE
	page_namespace = 1198
    AND
    rev_timestamp > (now() - INTERVAL 180 DAY + 0)   
    AND substring_index(page_title, '/', -1) in ('cs', 'hu', 'ro', 'it', 'ko', 'zh-yue', 'fi', 'th')
GROUP BY
	user, language
HAVING
    no_translations > 20
ORDER BY
	language, no_translations DESC;

SQL

Checking query status...