use metawiki_p;

SET @pageTitle = ("Tech/News/2024");

SELECT

    actor_name as user,

    page_title as pageTitle,

    substring_index(page_title, '/', -1) AS language,

    count(*) AS no_translations,

    CONCAT("https://meta.wikimedia.org/wiki/Special:Contributions/", actor_name) AS Centralauth

FROM

    page

LEFT JOIN

    revision ON (page_id = rev_page)

LEFT JOIN

    actor_revision ON (rev_actor = actor_id)    

WHERE

    page_namespace = 1198 # Within Translation name space

    AND rev_timestamp > (now() - INTERVAL 350 DAY + 0)   # Last 3 months

    AND REPLACE(page_title, "_", " ") like CONCAT(@pageTitle, "%")

    AND actor_name not like "%(WMF)" # Exclude staff

    AND actor_name not like "%Bot" # Exclude robots

​

GROUP BY

    user, language

ORDER BY

    language, no_translations DESC;