use fiwiki_p;

​

select 

    count(distinct(user_name)) as users ,

    count(distinct(log_title)) as pages,

    sum(page_id IS NULL) as deleted,

    count(distinct(ctd_id)) as content_translated_pages,

    sum(ctd_id IS NULL) as content_translated_deleted

from (

select 

    substring(user_name,1,30) as user_name, 

    user_editcount, 

    substring(log_title,1,30) as log_title, 

    p1.page_id as page_id,  

    ctd_id 

from 

    user, 

    logging_compat as l1 

    LEFT JOIN page as p1 ON l1.log_page=p1.page_id AND p1.page_namespace=0 

    LEFT JOIN change_tag ON ct_log_id=log_id 

    LEFT JOIN change_tag_def ON ct_tag_id=ctd_id AND ctd_name = "contenttranslation" 

where 

    user_registration > 20200014210707 

    and user_id=l1.log_user 

    and l1.log_type="create" 

    and l1.log_namespace=0 and log_id>11602240 

)

as t