[hrwiki] Missing article highly present on other languages

Fork of [itvoy] Missing article highly present on other languages by Andyrom75

This query is marked as a draft This query has been published by Ivi104.

wikidata items with a link to enwiki (excluding years and : in name) not present on hrwiki, sorted by number of interwiki links

SQL

SELECT CONCAT('Q',envoy.ips_item_id) AS ID, count(envoy.ips_site_id) AS Qty, envoy.ips_site_page AS Art
FROM (SELECT ips_item_id FROM wb_items_per_site WHERE ips_site_id LIKE '%wiki' ) AS allvoy,
(SELECT ips_item_id, ips_site_page, ips_site_id FROM wb_items_per_site WHERE ips_site_id = 'enwiki') AS envoy
WHERE allvoy.ips_item_id NOT IN (SELECT ips_item_id FROM wb_items_per_site WHERE ips_site_id = 'hrwiki')
AND allvoy.ips_item_id = envoy.ips_item_id
AND ips_site_page NOT LIKE '%:%'
AND NOT (ips_site_page REGEXP '^[0-9]{3,4}$' OR ips_site_page REGEXP '^AD [0-9]+$')
GROUP BY envoy.ips_item_id
ORDER BY Qty DESC
LIMIT 100

SQL

Checking query status...