SELECT 

    p.page_id,

    p.page_title,

    p.page_len,

    COUNT(DISTINCT ll.ll_lang) as translation_count,

    MAX(ll.ll_lang) as translated_language

FROM 

    page p

    -- Join with change_tag to get translation tags

    JOIN revision r ON r.rev_page = p.page_id

    JOIN change_tag ct ON ct.ct_rev_id = r.rev_id

    JOIN change_tag_def ctd ON ct.ct_tag_id = ctd.ctd_id

    -- Join with langlinks to get language information

    JOIN langlinks ll ON ll.ll_from = p.page_id

WHERE 

    -- Filter for main namespace

    p.page_namespace = 0

    -- Exclude redirects

    AND p.page_is_redirect = 0

    -- Check for translation tags

    AND ctd.ctd_name IN ('contenttranslation', 'sectiontranslation')

    -- Ensure the page exists

    AND p.page_len > 0

GROUP BY 

    p.page_id,

    p.page_title,

    p.page_len

HAVING 

    -- Filter for exactly one translation

    COUNT(DISTINCT ll.ll_lang) = 1

ORDER BY 

    p.page_len DESC

LIMIT 1000;