Content translation effectiveness on tewiki

SQL

use arwiki_p;

set @dbname = 'arwiki';

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 '202201%' 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 '202201%' 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 '202201%' and
    ar_parent_id = 0
);

set @deleted_cx = (
  select
    count(ar_id)
  from
    change_tag,
    archive
  where
    ar_namespace = 0 and
    ar_timestamp like '202201%' 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 %';
SQL

Checking query status...
