-- Get articles that have been translated using Content Translation

-- and count the number of languages they've been translated into

SELECT 

    p.page_title AS source_article,

    COUNT(DISTINCT ll.ll_lang) AS 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'

GROUP BY p.page_title

ORDER BY translation_count DESC;