SET @start_time = '20230101'; -- 1 Jan 2020

SET @end_time = '20230318'; -- 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;