SQL
AخA
USE arwiki_p;
SELECT eu_entity_id, en.term_text, COUNT(*)
FROM (SELECT * FROM wbc_entity_usage WHERE eu_aspect = "L.ar") AS eusage
JOIN wikidatawiki_p.page
ON page_namespace = 0 AND page_title = eu_entity_id
JOIN wikidatawiki_p.wb_entity_per_page
ON epp_entity_type = "item" AND epp_page_id = page_id
LEFT JOIN (SELECT * FROM wikidatawiki_p.wb_terms WHERE term_type = "label" AND term_language = "ar" AND term_entity_type = "item") AS cs
ON cs.term_entity_id = epp_entity_id
LEFT JOIN (SELECT * FROM wikidatawiki_p.wb_terms WHERE term_type = "label" AND term_language = "en" AND term_entity_type = "item") AS en
ON en.term_entity_id = epp_entity_id
WHERE cs.term_text IS NULL
GROUP BY eu_entity_id HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC, IF(en.term_text IS NULL, 1, 0), epp_entity_id
limit 500;
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.