SQL
x
set @dbname = 'igwiki';
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';
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.