with

_discussiontools as (
select it_id, it_itemname, itp_page_id, itid_id
from discussiontools_items
join discussiontools_item_pages
	on itp_items_id = it_id
join discussiontools_item_revisions
	on itr_items_id = it_id and itr_revision_id = itp_newest_revision_id
join discussiontools_item_ids
	on itid_id = itr_itemid_id

join page on page_id = itp_page_id and page_latest = itr_revision_id

-- Ignore placeholder headings on pages with no headings, which would all be duplicates of each other
where it_itemname != 'h-'
-- Ignore items where the newest revision containing them is not the latest revision of the page
and page_id is not null
-- Ignore items which are transcluded from another page
and itr_transcludedfrom is null
),

_counts as (
select
	left(it_itemname, 1) as type,
	number_of_copies,
	count(*) as number_of_items,
	group_concat(it_itemname order by rand() separator ' ' limit 3) as examples
from (
select it_itemname, count(*) as number_of_copies
from _discussiontools
group by it_itemname
) x
group by type, number_of_copies
),

_summary as (
select
	(select number_of_items from _counts where number_of_copies=1 and type='c') as uniq_comments,
	-- (select sum(number_of_items) from _counts where number_of_copies!=1 and type='c') as nonuniq_comments,
	(select sum(number_of_items*number_of_copies) from _counts where number_of_copies!=1 and type='c') as nonuniq_comments,
	(select number_of_items from _counts where number_of_copies=1 and type='h') as uniq_headings,
	-- (select sum(number_of_items) from _counts where number_of_copies!=1 and type='h') as nonuniq_headings,
	(select sum(number_of_items*number_of_copies) from _counts where number_of_copies!=1 and type='h') as nonuniq_headings
)

select
	uniq_comments, nonuniq_comments, nonuniq_comments/(uniq_comments+nonuniq_comments) as '%nonuniq_comments',
	uniq_headings, nonuniq_headings, nonuniq_headings/(uniq_headings+nonuniq_headings) as '%nonuniq_headings'
from _summary