Fork of Content translation survival rate by Zache
This query is marked as a draft This query has been published by UOzurumba (WMF).

SQL

AخA
 
SELECT 
    floor(log_timestamp/10000000000) as year,
    sum(log_id IS NOT NULL) as total_created,
    sum(ct_id IS NOT NULL) as cx_created,
    sum(ct_id IS NULL) as non_cx_created,
    sum(page_id IS NOT NULL) as total_pages_exists,
    sum(page_id IS NOT NULL AND ct_id IS NOT NULL) as total_cx_pages_exists,    
    sum(page_id IS NOT NULL AND ct_id IS NULL) as total_non_cx_pages_exists,
    ( sum(page_id IS NOT NULL) / sum(log_id IS NOT NULL) ) as total_ratio,
    ( sum(page_id IS NOT NULL AND ct_id IS NOT NULL) / sum(ct_id IS NOT NULL) ) as cx_ratio,
    ( sum(page_id IS NOT NULL AND ct_id IS NULL) /  sum(ct_id IS NULL) ) as non_cx_ratio
FROM
(SELECT
    log_id,
    log_page,
    page_id,
    log_timestamp,
    log_actor,
    ct_id
FROM
    logging 
        LEFT JOIN page ON page_id = log_page AND page_is_redirect IS NOT true
        LEFT JOIN change_tag ON ct_log_id=log_id AND ct_tag_id IN ( 
          SELECT ctd_id FROM change_tag_def WHERE ctd_name IN ("contenttranslation", "contenttranslation-v2") 
        ),
    actor_logging,
    user
WHERE
    log_namespace=0
    AND log_action="create"
    AND actor_id=log_actor
    AND user_id=actor_user
    AND DATEDIFF(STR_TO_DATE(log_timestamp, "%Y%m%d%H%i%s"), STR_TO_DATE(user_registration, "%Y%m%d%H%i%s")) < 365
GROUP BY log_id
)
as t
GROUP BY year
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...