User:Ijon/Queries

Queries tutorials edit

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

Asaf's Query examples edit

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

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) edit

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 edit

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 edit

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 edit

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 edit

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 edit

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 edit

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 edit

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 edit

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 edit

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 edit

(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 edit

Asaf's gradually complicating examples using Estonia and Estonians

Other people's queries to stealadapt edit

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.)