Untitled query #87893

SQL

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;
SQL

Checking query status...
