Find most-depicted Wikidata items that have no image

Question

Wikimedia Commons has a new feature where a picture can have metadata saying it depicts a given Wikidata item.

For instance, https://commons.wikimedia.org/wiki/File:Grandpark_Plaza.jpg depicts https://www.wikidata.org/wiki/Q11303 (skyscraper).

My question: How to find the Wikidata items that:

• Are used as a "depicts" value by a lot of Wikimedia Commons files
• Do NOT have a P18 (image) property

It is tricky because it involves two Wikibase servers: Wikidata, and the Structured Data on Commons (SDoC)'s Wikibase server. I am open to any solution, SPARQL or not, hosted or not, open source or not.

Answer 1

You can do a federated query with the Wikimedia Commons endpoint and the Wikidata endpoint. The query below does take a while to complete - though.

SELECT 
  ?count
  ?depicted ?depictedLabel
  ?example_file
WHERE {
  {
    SELECT 
      (COUNT(*) AS ?count)
      ?depicted
      (SAMPLE(?file) AS ?example_file)
    WHERE {
      ?file wdt:P180 ?depicted .
    }
    GROUP BY ?depicted
    ORDER BY DESC(?count)
    LIMIT 100
  }
  SERVICE <https://query.wikidata.org/sparql> {
    MINUS { ?depicted wdt:P18 [] }
    OPTIONAL {?depicted rdfs:label ?depictedLabel FILTER (lang(?depictedLabel) = 'en') } .
  }
}
ORDER BY DESC(?count)

Short link: https://w.wiki/5MyH