User:Hdevine825/Queries

A place to store some useful Wikidata queries.

Paralympics Queries

A few queries to identify and fill in Property:P1532 Country for Sport for Paralympic athletes.

National Paralympic/Olympic Committee Databases

SELECT ?database ?databaseLabel ?dbCountry ?dbCountryLabel ?dbQID ?dbQIDLabel ?statedIn ?statedInLabel WHERE {
  ?database wdt:P31 wd:Q30225604. #all NOC database properties
  ?database wdt:P17 ?dbCountry. #get country
  OPTIONAL{
    ?dbQID wdt:P1687 ?database. #get QIDs that use properties from line 2 
    ?dbQID wdt:P31/wdt:P279* wd:Q7094076. #filter down to online databases and subclasses (removes a lot of wiki templates)
  }
  OPTIONAL{ ?database wdt:P9073 ?statedIn. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Athletes with Paralympic.org ID and Olympic Committee ID but no Country for Sport

Uses national Paralympic committee identifiers to infer country for sport. Goal of importing the country results of this query as Property:P1532 Country for Sport with stated in used as a reference.

SELECT DISTINCT ?athlete ?athleteLabel ?dbCountry ?dbCountryLabel ?statedInRef ?statedInRefLabel WHERE {
  ?athlete wdt:P7550 ?paraID. #retrieve all athletes with a global paralympic id
  MINUS{?athlete wdt:P1532 ?country.} #remove those with country for sport specified 
  ?database wdt:P31 wd:Q30225604. #find all national database id properties related to paralympics 
  ?database wdt:P17 ?dbCountry. #find countries for those databases
  ?database wikibase:directClaim ?databaseProp.
  ?athlete ?databaseProp ?countryID. #find athletes who appear in those databases
  OPTIONAL{
    ?dbQID wdt:P1687 ?database.
    ?dbQID wdt:P31/wdt:P279* wd:Q7094076.
  }
  OPTIONAL{ ?database wdt:P9073 ?statedIn. }
  BIND(COALESCE(?statedIn, ?dbQID) as ?statedInRef)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Burials for a Cemetery with Coordinates and Count of Sitelinks

#Cemetery Burials with coordinates and sitelink count
SELECT 
  ?item 
  ?itemLabel
  ?itemDescription
  ?location
  ( COUNT( ?sitelink ) AS ?sitelink_count ) 
WHERE 
{
  ?item wdt:P31 wd:Q5. #instance of human
  ?item wdt:P119 wd:Q6723847. #place of burial, insert cemetery qid here
  OPTIONAL{?item p:P119 [ ps:P119 ?placeburial; pq:P625 ?location ].} #return the coordinate location qualifier of place of burial if it exists
  ?sitelink schema:about ?item. #get site links
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } #default label thing
  Optional{?item schema:description"en".} #get English description
}
GROUP BY ?item ?itemLabel ?location ?itemDescription #group items, if other field are to be shown they need to be added to this
HAVING ( COUNT( ?sitelink ) > 0 ) #only show if there is at least 1 sitelink
ORDER BY DESC( COUNT( ?sitelink ) ) #sort by number of sitelinks for each item

Try it!

List of Schools by Research Papers with Specific Subjects

Not fully accurate to what schools produce the papers since it uses all schools the authors attended.

SELECT ?school ?schoolLabel ?subject ?subjectLabel (count(distinct(?paper)) as ?countPapers)
WHERE {
  {?subject wdt:P279* wd:Q638328.} #subject is dynamical system
  UNION
  {?subject wdt:P279* wd:Q1787424.} #subject is chaos
  UNION
  {?subject wdt:P279* wd:Q185961.} #subject is time perception
  {?paper wdt:P31/wdt:P279* wd:Q13442814.} #is a scholarly article
  UNION
  {?paper wdt:P31/wdt:P279* wd:Q18918145.} #is a academic journal article
  ?paper wdt:P921 ?subject;
         wdt:P50 ?author.
  {?author wdt:P69 ?school.} #author educated at
  UNION
  {?author wdt:P108 ?school.} #author employed at
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
group by ?school ?schoolLabel ?subject ?subjectLabel
order by desc(?countPapers)

Try it!

Video Games by Number of Cast Members

SELECT ?item ?itemLabel ?languageLabel (count(?actor) as ?countActors) (count(?charName) as ?countCharsNames) (count(?charItem) as ?countCharItems)
WHERE 
{
  ?item wdt:P31 wd:Q7889.
  ?item (p:P725|p:161) ?cast.
  ?cast (ps:P725|ps:161) ?actor.
  optional{?cast pq:P4633 ?charName.}
  optional{?cast pq:P453 ?charItem.}
  optional{?cast pq:P407 ?language.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
group by ?item ?itemLabel ?languageLabel
order by DESC(?countActors)

Try it!

Film, TV, and Video Games set in Seattle by Release Year

#first line for testing, second for bar chart
#SELECT ?item ?itemLabel ?typeLabel ?year_ ?locationLabel Where {
SELECT (str(SAMPLE(?year_)) AS ?year ) (COUNT( ?item) as ?count) ?typeLabel WHERE {
  VALUES ?type {wd:Q11424 wd:Q7889 wd:Q15416}
  ?item wdt:P31/wdt:P279* ?type.
  ?item wdt:P840 ?location.
  ?location wdt:P131* wd:Q5083.
  ?item wikibase:sitelinks ?linkcount.
  ?item wdt:P577 ?releaseDate.
  BIND(YEAR(?releaseDate) as ?year_).           
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,es,ar,fr" }
  FILTER( ?year_ > 1900)
}
#first line for testing, second for bar chart
#GROUP BY ?item ?itemLabel ?typeLabel ?year_ ?locationLabel
GROUP BY ?year_ ?count ?typeLabel
ORDER BY ASC(?year_)
#defaultView:BarChart

Try it!