Content translation survival rate

SQL

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")) < 30
GROUP BY log_id
)
as t
GROUP BY year
SQL

Checking query status...
