User:Ijon/Queries

Queries tutorials

• Video recording of 2-hour SPARQL tutorial (San Francisco, June 2018)

Asaf's Query examples

Breakdown of item types (instance of) for a given country (Israel, here)

The following query uses these:

• Items: Israel (Q801)   

• Properties: country (P17)   , instance of (P31)   

  # breakdown of item types (instance of) for a given country (Israel, here)
  SELECT ?instanceLabel (COUNT(?item) as ?count) 
  WHERE { 
    ?item wdt:P17 wd:Q801 . 
    ?item wdt:P31 ?instance . 
    ?instance rdfs:label ?instanceLabel  
    FILTER(LANG(?instanceLabel) = "he")
  }
  GROUP BY ?instanceLabel
  ORDER BY DESC(?count)
  LIMIT 30
  

Try it!

Albums known to be from a certain country (Israel, here)

The following query uses these:

• Items: album (Q482994)   , Israel (Q801)   

• Properties: instance of (P31)   , subclass of (P279)   , country of origin (P495)   , performer (P175)   , genre (P136)   

  # Albums known to be from Israel
  SELECT ?item ?itemLabel ?performerLabel ?genreLabel ?depth
  WHERE {
      ?item (wdt:P31/wdt:P279*) wd:Q482994;
            wdt:P495 wd:Q801;
            wikibase:statements ?depth.
    OPTIONAL {
      ?item wdt:P175 ?performer.
    }
    OPTIONAL {
      ?item wdt:P136 ?genre.
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],he". }
    }
  

Try it!

Ukrainian politicians by birth oblast

The following query uses these:

• Items: human (Q5)   , politician (Q82955)   , Ukraine (Q212)   

• Properties: instance of (P31)   , occupation (P106)   , country of citizenship (P27)   , place of birth (P19)   , located in the administrative territorial entity (P131)   , country (P17)   

  # Ukrainian politicians by birth oblast
  SELECT ?oblastLabel (COUNT(?thing) as ?total)
  WHERE 
  {
    ?thing wdt:P31 wd:Q5 . # human
    ?thing wdt:P106 wd:Q82955 . # politician
    ?thing wdt:P27 wd:Q212 . # Ukrainian citizen
    ?thing wdt:P19 ?place . # who has *some* place of birth
    ?place wdt:P131 ?oblast . # where the place belongs to an administrative entity
    ?oblast wdt:P17 wd:Q212 . # which is in Ukraine
    
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],uk,ru,pl,be,cs,bg,sr,en". }
  }
  GROUP BY ?oblastLabel
  ORDER BY DESC(?total)
  

Try it!

People born somewhere in Zhytomyr oblast, with fallback languages marked

The following query uses these:

• Items: human (Q5)   , Zhytomyr Oblast (Q40637)   

• Properties: instance of (P31)   , place of birth (P19)   , located in the administrative territorial entity (P131)   

  # People born somewhere in Zhytomyr oblast, with fallback languages marked
  SELECT ?shtuka ?shtukaLabel (LANG(?shtukaLabel) as ?lang) 
  WHERE 
  {
    { ?shtuka wdt:P31 wd:Q5 . # all the humans
    ?shtuka wdt:P19 ?place . # who have a place of birth
    
    ?place wdt:P131 wd:Q40637 .} # which is in Zhytomyr oblast
    
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],ru,pl,be,cs,bg,sr,en". }
  }
  

Try it!

Editors-in-chief and their employers

The following query uses these:

• Items: human (Q5)   , editor-in-chief (Q589298)   

• Properties: instance of (P31)   , position held (P39)   , employer (P108)   

  # Editors-in-chief and their employers
  SELECT ?editor ?editorLabel ?empLabel
  WHERE 
  {
    ?editor wdt:P31 wd:Q5.
    ?editor wdt:P39 wd:Q589298 . # holding editor-in-chief positions
    OPTIONAL {
              ?editor p:P39 ?prop .
             ?prop ps:P39 wd:Q589298 .
             ?prop pq:P108 ?emp . }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  

Try it!

Best-described items in Te Papa museum

The following query uses these:

• Items: Museum of New Zealand Te Papa Tongarewa (Q915603)   , painting (Q3305213)   

• Properties: collection (P195)   , location (P276)   , instance of (P31)   

  # best-described items in Te Papa museum
  SELECT DISTINCT ?q ?stcount WHERE {
    { ?q wdt:P195 wd:Q915603 .} # in Te Papa's collection 
    UNION 
    { ?q wdt:P276 wd:Q915603 .} # or "located in" Te Papa }
    # ?q wdt:P31 wd:Q3305213 . # limit to paintings (to not timeout)
    ?q wikibase:statements ?stcount .
  } GROUP BY ?q ?stcount
  ORDER BY DESC(?stcount)
  

Try it!

Most-used properties to describe poems

The following query uses these:

• Items: poem (Q5185279)   

• Properties: instance of (P31)   

  SELECT ?prop ?propLabel ?count 
  WHERE 
  {
      {
          SELECT ?prop (COUNT(DISTINCT ?item) AS ?count) WHERE {
             
             hint:Query hint:optimizer "None" .
             ?item wdt:P31 wd:Q5185279 .
             ?item ?p ?id .
             ?prop wikibase:directClaim ?p .
          }  GROUP BY ?prop
      }
    
      SERVICE wikibase:label {
          bd:serviceParam wikibase:language "en" .
      }
  } ORDER BY DESC (?count)
  

Try it!

Australian women covered in other Wikipedias but not in English

The following query uses these:

• Items: human (Q5)   , female (Q6581072)   , Australia (Q408)   

• Properties: instance of (P31)   , sex or gender (P21)   , country of citizenship (P27)   

  # Australian women covered in other Wikipedias but not in English
  SELECT ?person ?personLabel ?personDescription (COUNT(DISTINCT ?sitelink) as ?linkcount) WHERE {
    ?person wdt:P31 wd:Q5 . # human
    ?person wdt:P21 wd:Q6581072 . # woman
    ?person wdt:P27 wd:Q408 . # Australian
    ?sitelink schema:about ?person . # get Wikimedia pages about the person
    FILTER NOT EXISTS {
      ?links schema:about ?person . # get Wikimedia pages about the person
      ?links schema:isPartOf <https://en.wikipedia.org/> . # but exclude English Wikipedia.
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } GROUP BY ?person ?personLabel ?personDescription
  ORDER BY DESC(?linkcount) 
  LIMIT 20
  

Try it!

People who won the Nobel Prize more than once

The following query uses these:

• Items: human (Q5)   , Nobel Prize (Q7191)   

• Properties: instance of (P31)   , award received (P166)   

  # People who won the Nobel Prize more than once
  SELECT ?winnerLabel ?wins
  {
    {
      SELECT ?winner (COUNT(?prize) AS ?wins)
      WHERE  
      { 
        ?winner wdt:P31 wd:Q5 . # human
        ?winner wdt:P166 ?prize . # who has won some prize
        ?prize wdt:P31 wd:Q7191 . # and where that prize is a kind of Nobel prize
      }
      GROUP BY ?winner
      ORDER BY DESC(?wins)
    }
    FILTER( ?wins > 1)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  

Try it!

Population timeline of a city

The following query uses these:

• Items: Jerusalem (Q1218)   

• Properties: population (P1082)   , point in time (P585)   

  #defaultView:Timeline
  #title: Timeline of population size of Jerusalem, Israel
  SELECT ?pop ?time WHERE {
    wd:Q1218 p:P1082 [ ps:P1082 ?pop ; pq:P585 ?time ] .
  }
  

Timeline of population size of Jerusalem, Israel

Model items by type

The following query uses these:

• Properties: model item (P5869)   

  # model items
  SELECT ?conceptLabel ?item ?itemLabel 
  WHERE 
  {
    ?concept wdt:P5869 ?item.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY ?conceptLabel
  

Try it!

Most important missing item labels in a language

(The Twi language in this example)

The following query uses these:

• Items: Wikimedia page (Q58494026)   

• Properties: instance of (P31)   , subclass of (P279)   

  # 100 most significant items missing labels in Twi 
  # (change the "tw" below to another language code to get the same query for another language)
  
  SELECT ?item ?sitelinks WHERE {
    ?item wikibase:sitelinks ?sitelinks. hint:Prior hint:rangeSafe true.
    FILTER(?sitelinks >= 100) # use at-least-100-sitelinks as an indicator of significance
  
    MINUS { ?item wdt:P31/wdt:P279* wd:Q58494026} # exclude Wikimedia internal items (categories, templates, etc.)
  
    MINUS { ?item rdfs:label ?label FILTER(LANG(?label) = "tw") } # <-- change language here
  
  } ORDER BY DESC(?sitelinks)
  LIMIT 100
  

Try it!

Queries Asaf is too lazy to paste here

• Asaf's gradually complicating examples using Estonia and Estonians

Other people's queries to stealadapt

• queries by MKar
• queries by MartinPoulter
• the example queries featured in the Examples button on query.wikidata.org
• queries by Hsarrazin (descriptions in French)
• queries by Fuzheado
• Stats on SPARQL queries (ideas for properties to query etc.)