-- Get articles translated to Igbo using Content Translation tool

-- between 2014 and 2025, with translation timestamps and total translation count

SELECT 

    p.page_title AS source_article,

    r.rev_timestamp AS espanol_translation_date,

    COUNT(DISTINCT ll.ll_lang) AS total_translation_count

FROM page p

JOIN revision r ON p.page_id = r.rev_page

JOIN change_tag ct ON r.rev_id = ct.ct_rev_id

JOIN change_tag_def ctd ON ct.ct_tag_id = ctd.ctd_id

JOIN langlinks ll ON p.page_id = ll.ll_from

WHERE 

    p.page_namespace = 0  -- Main namespace articles only

    AND ctd.ctd_name = 'contenttranslation'

    AND EXISTS (

        SELECT 1 

        FROM langlinks ll2 

        WHERE ll2.ll_from = p.page_id 

        AND ll2.ll_lang = 'es'  -- 'es' is the language code for espanol

    )

    AND r.rev_timestamp >= '20140101000000'

    AND r.rev_timestamp <= '20251231235959'

GROUP BY p.page_title, r.rev_timestamp

ORDER BY r.rev_timestamp DESC;

​

​

​