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;