-- Get articles translated to Igbo using Content Translation tool

-- along with first translation timestamp and total translation count

SELECT 

    p.page_title AS source_article,

    MIN(r.rev_timestamp) AS first_igbo_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 = 'ig'  -- 'ig' is the language code for Igbo

    )

GROUP BY p.page_title

ORDER BY first_igbo_translation_date DESC;

​