Untitled query #61193

SQL

SELECT sitelink.ips_site_page AS category, CONCAT("Q",items.ips_item_id) AS category_item,
	GROUP_CONCAT(DISTINCT term_full_entity_id SEPARATOR ', ') AS taxon_item,
	COUNT(items.ips_site_page) AS number_of_sitelinks
	FROM page AS categories
	JOIN (SELECT * FROM wikidatawiki_p.wb_terms WHERE term_type IN ("label", "alias") AND term_entity_type = "item") AS terms
		ON categories.page_namespace = 14 AND categories.page_title = term_text
    JOIN wikidatawiki_p.page AS entities
    	ON entities.page_namespace = 0 AND term_full_entity_id = entities.page_title
	JOIN (SELECT * FROM wikidatawiki_p.pagelinks WHERE pl_title IN ("Q16521", "Q310890") AND pl_namespace = 0) AS links
		ON links.pl_from = entities.page_id
	JOIN wikidatawiki_p.wb_items_per_site AS sitelink
		ON sitelink.ips_site_id = "cswiki" # change this to your wiki identifier
        	AND sitelink.ips_site_page = CONCAT("Kategorie:", categories.page_title) # change this to localised prefix
	JOIN wikidatawiki_p.wb_items_per_site AS items
		ON items.ips_item_id = sitelink.ips_item_id
GROUP BY sitelink.ips_site_page HAVING number_of_sitelinks < 10 # change this to what you consider enough
ORDER BY COUNT(items.ips_site_page) DESC, term_text;
SQL

Checking query status...
