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

Fork of Deletion Ratios for one wiki during 2020 by Pginer-WMF

This query is marked as a draft This query has been published by Neil Shah-Quinn (WMF).

For one wiki, shows how many articles were created using Content Translation (CX articles) and how frequently they were deleted, compared to articles created using other methods (non-CX articles).

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
)
SELECT
    cx,
    non_cx
FROM nondeleted_new_articles

SQL

Checking query status...