Untitled query #33532

SQL

use tawiki_p;

set @dbname = 'tawiki';

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

set @deleted_cx = (
  select
    count(ar_id)
  from
    change_tag,
    archive
  where
    ar_namespace = 0 and
    ar_timestamp like '201701%' 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,
  concat(((@deleted_cx / @created_cx) * 100), '%') deleted_cx_ratio,
  concat(((@deleted_non_cx / @created_non_cx) * 100), '%') deleted_non_cx_ratio;
SQL

Checking query status...
