details.sql
matmarex (Bartosz Dziewoński)
Actions

Authored By

	• matmarex
	Jul 17 2023, 7:59 PM

Size

1 KB

Referenced Files

None

Subscribers

None

details.sql
View Options

	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
	)

	select number_of_copies, number_of_items, examples from _counts
	where number_of_copies > 1
	and type = 'c'
	order by number_of_copies desc