This query is marked as a draft This query has been published by Matěj Suchánek.

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.

Checking query status...