SQL
AخA
USE cswiki_p; # change this to your project
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"
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 COUNT(items.ips_site_page) < 4 # change this to what you consider enough
ORDER BY COUNT(items.ips_site_page) DESC, term_text;
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.