Fork of Items without demanded cs label by usage by Matěj Suchánek
This query is marked as a draft This query has been published by FShbib.

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.

Checking query status...