Replicas browser and optimizer
This query is marked as a draft
This query has been published
Example data for task https://phabricator.wikimedia.org/T215397 . Shows ratio of main namespace articles that were created using Content Translation and not using Content Translation and subsequently deleted. This sample works for the Hungarian Wikipedia (huwiki) for January 2016 (201601).
use huwiki_p; set @dbname = 'huwiki'; set @ct_tag_id = ( select ctd_id from change_tag_def where ctd_name = 'contenttranslation' ); set @created_total = ( select count(rev_id) from page, revision where page_id = rev_page and page_namespace = 0 and rev_timestamp like '201601%' and rev_parent_id = 0 ); set @created_cx = ( select count(rev_id) from change_tag, page, revision where page_id = rev_page and page_namespace = 0 and rev_id = ct_rev_id and rev_timestamp like '201601%' and rev_parent_id = 0 and ct_tag_id = @ct_tag_id ); set @created_non_cx = ( select (@created_total - @created_cx) ); set @deleted_total = ( select count(ar_id) from archive where ar_namespace = 0 and ar_timestamp like '201601%' and ar_parent_id = 0 ); set @deleted_cx = ( select count(ar_id) from change_tag, archive where ar_namespace = 0 and ar_timestamp like '201601%' and ar_parent_id = 0 and ct_tag_id = @ct_tag_id and ar_rev_id = ct_rev_id ); set @deleted_non_cx = ( select (@deleted_total - @deleted_cx) ); select @dbname, @created_cx 'New CX articles', @created_non_cx 'New non-CX articles', @deleted_cx 'Deleted CX articles', @deleted_non_cx 'Deleted non-CX articles', concat(((@deleted_cx / @created_cx) * 100), '%') 'Deleted CX %', concat(((@deleted_non_cx / @created_non_cx) * 100), '%') 'Deleted non-CX %';
By running queries you agree to the
and you irrevocably agree to release your SQL under
All SQL code is licensed under
Checking query status...
Report a bug!
Wikimedia Cloud VPS
, written in