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 ('te', 'hi', 'ta', 'ml', 'kn', 'bn', 'mr', 'ur')

GROUP BY

    user, language

HAVING

    no_translations > 9

ORDER BY

    language, no_translations DESC;