Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
Deletion rates of CX and non-CX articles for a wiki
by
Neil Shah-Quinn (WMF)
This query is marked as a draft
This query has been published
by
MNeisler (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).
Toggle Highlighting
SQL
SET @start_time = '20220101'; -- 1 Jan 2020 SET @end_time = '20230101'; -- 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;
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...