-- Get articles that have been translated using Content Translation

-- with pagination (100 results per page)

-- Replace {offset} with (page_number - 1) * 100

-- e.g., for page 1: offset = 0, page 2: offset = 100, page 3: offset = 200, etc.

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

LIMIT 100 OFFSET {offset};