User:Frog23/My Queries

Things To Do

Instances of Year that are not fixed in time and are not assigned to a calendar

The following query uses these:

• Items: year (Q577)   , Hebrew calendar (Q44722)   , Islamic calendar (Q28892)   , Solar Hijri calendar (Q950135)   , North Korean calendar (Q3353159)   

• Properties: point in time (P585)   , start time (P580)   , end time (P582)   , part of (P361)   , instance of (P31)   

  SELECT ?year ?yearLabel WHERE {
    ?year p:P31 ?statement.
    ?statement ps:P31 wd:Q577.
    FILTER NOT EXISTS {?year wdt:P585 ?time1 }
    FILTER NOT EXISTS {?year wdt:P580 ?time2 }
    FILTER NOT EXISTS {?year wdt:P582 ?time3 }
    FILTER NOT EXISTS {?statement pq:P361 wd:Q44722 }
    FILTER NOT EXISTS {?year wdt:P361 wd:Q44722 }
    FILTER NOT EXISTS {?statement pq:P361 wd:Q28892 }
    FILTER NOT EXISTS {?year wdt:P361 wd:Q28892 }
    FILTER NOT EXISTS {?statement pq:P361 wd:Q950135 }
    FILTER NOT EXISTS {?year wdt:P361 wd:Q950135 }
    FILTER NOT EXISTS {?statement pq:P361 wd:Q3353159}
    FILTER NOT EXISTS {?year wdt:P361 wd:Q3353159 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  

Try it!

Wikidata Editing and Maintenance

Instances of something by the number of properties they have

(in this case all instances of a Roller Coaster)

The following query uses these:

• Items: roller coaster (Q204832)   

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

  SELECT ?item ?itemLabel (COUNT (?prop) as ?count) WHERE{
    ?item wdt:P31/wdt:P279* wd:Q204832 ;
          ?prop ?statement.
    ?property wikibase:directClaim ?prop.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  GROUP BY $item ?itemLabel
  ORDER BY DESC(?count)
  

Try it!

Most common properties used by all of the instances of something

The following query uses these:

• Items: roller coaster (Q204832)   

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

  SELECT ?property ?propertyLabel (COUNT (?item) as ?count) WHERE{
    ?item wdt:P31/wdt:P279* wd:Q204832 ;
          ?prop ?statement.
    ?property wikibase:directClaim ?prop.
    FILTER (?property != wd:P31)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" .}
    
  }
  GROUP BY $property ?propertyLabel
  ORDER BY DESC(?count)
  

Try it!

Properties that are used to describe themselves

The following query uses these:

# Make a list of the properties that are used to describe themselves
SELECT ?property ?propertyLabel ?propertyDescription (COUNT(?propertyclaim) AS ?count) WHERE { 

  ?property a wikibase:Property .
  ?property wikibase:claim ?propertyclaim .

  ?property ?propertyclaim [] .
	SERVICE wikibase:label {            # ... include the labels
		bd:serviceParam wikibase:language "en" .
	}   
} GROUP BY ?property ?propertyLabel ?propertyDescription ORDER BY DESC (?count)

Try it!

Just for Fun

Age of Musicians at their death

The following query uses these:

• Items: human (Q5)   , musician (Q639669)   

• Properties: instance of (P31)   , occupation (P106)   , subclass of (P279)   , date of birth (P569)   , date of death (P570)   

  #defaultView:BarChart
  # Query to find all musicians who have already died 
  # calculate their age (full years) at death
  # count how many of them died at each age
  
  SELECT ?age  (COUNT (DISTINCT ?a) AS ?count) WHERE {
    ?a wdt:P31 wd:Q5 . #instance of human
    ?a wdt:P106/wdt:P279 wd:Q639669 . #occupation a subclass of musician
    ?a p:P569/psv:P569 ?birth_date_node . 
    ?a p:P570/psv:P570 ?death_date_node .
    ?birth_date_node wikibase:timeValue ?birth_date .
    ?death_date_node wikibase:timeValue ?death_date .
    FILTER(?age > 10 && ?age < 100) . #ignore outlyers, several of which are probably errors
    BIND( year(?death_date) - year(?birth_date) - if(month(?death_date)<month(?birth_date)
  

Try it!

see also https://twitter.com/frog23_net/status/852457196361326592

Beetles named after The Beatles

(none in WD as of 2017-04-23)

The following query uses these:

• Items: taxon (Q16521)   , beetles (Q22671)   , The Beatles (Q1299)   

• Properties: instance of (P31)   , parent taxon (P171)   , has part(s) (P527)   , named after (P138)   

  SELECT ?beetle ?beetleLabel{
  	?beetle wdt:P31 wd:Q16521 . #?beetle <instances of> <Taxon>
  	?beetle wdt:P171* wd:Q22671 . #?beetle <parent taxon>* (= higher taxon) <Beetle>
  	wd:Q1299 wdt:P527 ?beatle . #<The Beatles> <has part> ?beatle
  	?beetle wdt:P138 ?beatle . #?beetle <named after> ?beetle
  	SERVICE wikibase:label {bd:serviceParam wikibase:language "en"}
  }
  

Try it!

Movies with past futures

The following query uses these:

• Items: film (Q11424)   

• Properties: instance of (P31)   , subclass of (P279)   , start time (P580)   , point in time (P585)   , publication date (P577)   , set in period (P2408)   , earliest date (P1319)   , latest date (P1326)   

  #movies which were set in the future when they were releast, but this future is now in the past
  SELECT ?film ?filmLabel  (MIN(?pubDate) AS ?pubDate) ?periodTime WHERE {
    #instance of film (or any subclass)
    ?film wdt:P31/wdt:P279* wd:Q11424.
    #set in a period
    ?film p:P2408 ?periodStatement.
    ?periodStatement ps:P2408 ?period.
    #either the period statement has an earliest date (P1319) or latest date (P1326) qualifier
    OPTIONAL { ?periodStatement pq:P1319 ?periodTime1. }
    OPTIONAL { ?periodStatement pq:P1326 ?periodTime2. }
    #or the period has a start time(P580) or a point in time (P585)
    OPTIONAL { ?period wdt:P580 ?periodTime3. }
    OPTIONAL { ?period wdt:P585 ?periodTime4. }
    BIND(COALESCE(?periodTime1, ?periodTime2, ?periodTime3, ?periodTime4) AS ?periodTime)
  
    #film has a publication date
    ?film wdt:P577 ?pubDate.
    #period is after publication date but before current time
    FILTER ((?periodTime >= ?pubDate) && (?periodTime <= NOW()))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  GROUP BY ?film ?filmLabel ?periodTime
  

Try it!

However a lot are missing w:List of stories set in a future now past

Actors or Actresses who have won an Oscar (Acadamy Award) for playing an Oscar winner

The following query uses these:

• Items: Academy Awards (Q19020)   

• Properties: award received (P166)   , instance of (P31)   , cast member (P161)   , for work (P1686)   , character role (P453)   

  #actors or actresses who have won an Oscar (Acadamy Award) for playing an Oscar winner
  SELECT DISTINCT ?actorA ?actorALabel ?movie ?movieLabel ?actorB ?actorBLabel WHERE {
      ?actorA wdt:P166/wdt:P31 wd:Q19020.
      ?award wdt:P31 wd:Q19020.
      ?actorB p:P166 ?awardStatement.
      ?awardStatement ps:P166 ?award.
      #put a comment in front of the line below to show all oscar winners portait by oscar winners
      ?awardStatement pq:P1686 ?movie.
      ?movie p:P161 ?castStatement.
      ?castStatement ps:P161 ?actorB.
      ?castStatement pq:P453 ?actorA.
      FILTER (?actorA != ?actorB) #this line is only in here to filter out actors/actresses who play themselves when the condition is removed that the Oscar had to be won for the same movie
    
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  

Try it!

People who are buried in the places they created/designed/build

The following query uses these:

• Properties: place of burial (P119)   , subproperty of (P1647)   

  SELECT ?person ?personLabel ?place ?placeLabel WHERE {
  	hint:Query hint:optimizer "None" .
  	?person wdt:P119 ?place.
    
  	?property wdt:P1647*  wd:P170 .
  	?property wikibase:statementProperty ?v .
  	?property wikibase:claim ?p .
    
    	?place ?p ?wds .
  	?wds ?v ?person .
  
  	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  

Try it!