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 '2020%' 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 '2020%' 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 '2020%' and

    ar_parent_id = 0

);

​

set @deleted_cx = (

  select

    count(ar_id)

  from

    change_tag,

    archive

  where

    ar_namespace = 0 and

    ar_timestamp like '2020%' 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 %',

  concat( ((@deleted_non_cx / @created_non_cx) * 100) - ((@deleted_cx / @created_cx) * 100),'%') 'Deletion % difference';

​