Deletion rates of CX and non-CX articles for a wiki

SQL

SET @start_time = '20200101'; -- 1 Jan 2020
SET @end_time = '20210101'; -- 1 Jan 2021
SET @cx_tag_id = (
	SELECT ctd_id
	FROM change_tag_def
	WHERE ctd_name = 'contenttranslation'
);

WITH nondeleted_new_articles AS (
	SELECT
		SUM(IF(ct_tag_id = @cx_tag_id, 1, 0)) AS cx,
		SUM(IF(ct_tag_id IS NULL, 1, 0)) AS non_cx
  	FROM revision
  	LEFT JOIN page ON rev_id = rev_page
	LEFT JOIN change_tag ON rev_id = ct_rev_id
		AND ct_tag_id = @cx_tag_id
	WHERE
    	page_namespace = 0
    	AND rev_parent_id = 0
		AND rev_timestamp BETWEEN @start_time AND @end_time
), deleted_new_articles AS (
	SELECT
		SUM(IF(ct_tag_id = @cx_tag_id, 1, 0)) AS cx,
		SUM(IF(ct_tag_id IS NULL, 1, 0)) AS non_cx
  	FROM archive
	LEFT JOIN change_tag ON ar_rev_id = ct_rev_id
		AND ct_tag_id = @cx_tag_id
	WHERE
		ar_namespace = 0
  		AND ar_parent_id = 0
		AND ar_timestamp BETWEEN @start_time AND @end_time  
), stats AS (
	SELECT
		nondeleted_new_articles.cx + deleted_new_articles.cx AS cx_new_articles,
		deleted_new_articles.cx / (nondeleted_new_articles.cx + deleted_new_articles.cx) AS cx_deletion_rate,
		nondeleted_new_articles.non_cx + deleted_new_articles.non_cx AS non_cx_new_articles,
		deleted_new_articles.non_cx / (nondeleted_new_articles.non_cx + deleted_new_articles.non_cx) AS non_cx_deletion_rate
	FROM deleted_new_articles
	CROSS JOIN nondeleted_new_articles
)
SELECT
	cx_new_articles AS 'CX new articles',
    CONCAT(ROUND(cx_deletion_rate * 100), '%') AS 'CX deletion rate',
    non_cx_new_articles AS 'non-CX new articles',
    CONCAT(ROUND(non_cx_deletion_rate * 100), '%') AS 'non-CX deletion rate',
	CONCAT(ROUND(cx_deletion_rate / non_cx_deletion_rate * 100), '%') AS 'CX deletion rate, vs. non-CX'
FROM stats;
SQL

Checking query status...
