#get list of articles that were created, in step with use of the article translation tool 
SELECT
    page_id, 
    revision_tags
FROM wmf.mediawiki_history
WHERE
    snapshot = "{MWH_SNAPSHOT}"
    AND event_timestamp >="{contest_start}"
    AND event_timestamp <"{contest_end}"
    AND page_namespace = 0
    AND event_entity = 'page'
    AND event_type = 'create'
    AND revision_is_identity_reverted = False 
    AND revision_is_deleted_by_page_deletion = False
    AND array_contains(revision_tags, "contenttranslation")   
    AND wiki_db = '{wiki_db}' 
    AND page_id IN {clean_pageids}
GROUP BY 
    page_id, revision_tags

#get list of articles that have an edit associated with the content translation tool
SELECT
    page_id, 
    revision_tags
FROM wmf.mediawiki_history
WHERE
    snapshot = "{MWH_SNAPSHOT}"
    AND event_timestamp >="{contest_start}"
    AND event_timestamp <"{contest_end}"
    AND page_namespace = 0
    AND event_entity = 'revision'
    AND revision_is_identity_reverted = False 
    AND revision_is_deleted_by_page_deletion = False
    AND array_contains(revision_tags, "contenttranslation")   
    AND wiki_db = '{wiki_db}' 
    AND page_id IN {clean_pageids}
GROUP BY 
    page_id, revision_tags