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

SQL

SET @start_time = '20230401'; -- 1 Jan 2020
SET @end_time = '20230701'; -- 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_page = page_id
    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 - 1) * 100), '%') AS 'CX deletion rate, vs. non-CX'
FROM stats;
SQL

Checking query status...
