Wikidata:Request a query

Request a query

This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you.

For sample queries, see Examples and Help:Dataset sizing. Property talk pages include also summary queries for these.

For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries and Wikidata:SPARQL query service/query optimization.

Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL.

To report an issue about the Query Service (interface, results views, export...) please see Wikidata:Contact the development team/Query Service and search.

Translate this header box!

Start a new discussion

On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2024/05.

Entities with articles in many languages but not English.[edit]

How can I find the Wikidata entities with the most wiki links which doesn't have an English page? (Or, more generally, the Wikidata entities with the most interwiki links which does not have a link in such-and-such a language.) Grendelkhan (talk) 03:57, 23 April 2024 (UTC)[reply]

I don't have a direct solution, but I did identify some parts:

You can select the count of some query, as in the example "Number of humans in wikidata"
The current top topic is "largest lakes no enwiki", the solution at https://w.wiki/9kqs may help you with figuring out how to detect whether there is no English wikipedia. It looks like that is this codeblock:

optional {
:   ?article schema:about ?item ; 
:    schema:isPartOf <https://en.wikipedia.org/> .
:  }
:  filter(!bound(?article))

Try it!

I think for items linked to a wikipedia page, for each language you get one rdfs:label in the article, for the title of the article in that language. For example here for the United Nations item: https://w.wiki/9seF

I guess the total query would need to select the item ID and number of those labels, while excluding the ones which are not allowed using the filter. I tried combining it all, but my attempt seems to run forever even with just a few items: https://w.wiki/9seW M.alten.tue (talk) 13:18, 25 April 2024 (UTC)[reply]

I think you've run into phabricator:T120198. There's a different way of doing it; see mw:Wikibase/Indexing/RDF Dump Format#Sitelinks. I unfortunately don't understand enough SPARQL to implement this, but this looks promising, maybe? Grendelkhan (talk) 22:54, 5 May 2024 (UTC)[reply]

Album tracks[edit]

Could someome make a query that shows all the tracks for Cultist Simulator: Original Soundtrack (Q125506146) + all compositions that the tracks are based on. --Trade (talk) 10:06, 4 May 2024 (UTC)[reply]

url queries of wikipedia urls take forever.[edit]

not a query request, but a question:

Why does this query take ~212ms:

SELECT ?url ?item WHERE {
  VALUES ?url {
    <https://www.podchaser.com/podcasts/this-american-life-399612>
    <https://www.podchaser.com/podcasts/this-american-life-399612/>
    <http://www.podchaser.com/podcasts/this-american-life-399612>
    <http://www.podchaser.com/podcasts/this-american-life-399612/>
    <http://podchaser.com/podcasts/this-american-life-399612>
    <http://podchaser.com/podcasts/this-american-life-399612/>
    <https://www.podchaser.com/>
    <http://www.podchaser.com/>
    <http://podchaser.com/>
    <https://www.podchaser.com>
    <http://www.podchaser.com>
    <http://podchaser.com>
  }
  ?item ?predicate ?url.
  ?property wikibase:directClaim ?predicate.
}

Try it!

while this shorter query takes more then 35s?

SELECT ?url ?item WHERE {
  VALUES ?url {
    <https://en.wikipedia.org/wiki/Double_bond>
    <https://en.wikipedia.org/wiki/Double_bond/>
    <http://en.wikipedia.org/wiki/Double_bond>
    <http://en.wikipedia.org/wiki/Double_bond/>
    <https://en.wikipedia.org/>
    <http://en.wikipedia.org/>
    <https://en.wikipedia.org>
    <http://en.wikipedia.org>
  }
  ?item ?predicate ?url.
  ?property wikibase:directClaim ?predicate.
}

Try it!

In both cases I'm only looking url statements that point to these urls. can I make both queries fast while yielding the same results? –Shisma (talk) 14:03, 4 May 2024 (UTC)[reply]

Some times the optimizer makes bad guesses about which order to do things. It's been said that starting with small sets gives good performance, but really what you want is to keep the size of the intermediate joins small throughout the query and especially at the start. Here it will run things in order of appearance. For your first query it will start with a set of 2 items and combine with a set of 11K items, where on the second query it will start with a set of 9.8M items, then it will combine this with a second set of 11K items. To fix the second query you can replace the last line with "?property wikibase:directClaim ?predicate. hint:Prior hint:runFirst true .". Infrastruktur (talk) 14:58, 4 May 2024 (UTC)[reply]

interesting. the hint, makes the second query faster, but the first query becomes slower. since I'm generating these queries programmaticaly I would need a heuristic to decide whether the hint should be present or not. why does the second query it start with a set of 9.8M items in the first place? I assume there aren't 9.8M statements with these urls… are there? – Shisma (talk) 15:13, 4 May 2024 (UTC)[reply]

I think it matches the basic graph pattern ?s schema:isPartOf <https://en.wikipedia.org/> that the sitelinks use. Infrastruktur (talk) 15:21, 4 May 2024 (UTC)[reply]

sounds reasonable since this query is much faster

SELECT ?url ?item WHERE {
  VALUES ?url {
    <https://en.wikipedia.org/wiki/Double_bond>
    <https://en.wikipedia.org/wiki/Double_bond/>
    <http://en.wikipedia.org/wiki/Double_bond>
    <http://en.wikipedia.org/wiki/Double_bond/>
    #<https://en.wikipedia.org/>
    <http://en.wikipedia.org/>
    <https://en.wikipedia.org>
    <http://en.wikipedia.org>
  }
  ?item ?predicate ?url.
  ?property wikibase:directClaim ?predicate.
}

Try it!

–Shisma (talk) 15:26, 4 May 2024 (UTC)[reply]

can I explicitly exclude sitelinks? – Shisma (talk) 15:23, 4 May 2024 (UTC)[reply]

Sure.

select ?url
where {
  {
    select * where {
      hint:SubQuery hint:optimizer "None".
      VALUES ?url {
<https://www.podchaser.com/podcasts/this-american-life-399612>
<https://www.podchaser.com/podcasts/this-american-life-399612/>
<http://www.podchaser.com/podcasts/this-american-life-399612>
<http://www.podchaser.com/podcasts/this-american-life-399612/>
<http://podchaser.com/podcasts/this-american-life-399612>
<http://podchaser.com/podcasts/this-american-life-399612/>
<https://www.podchaser.com/>
<http://www.podchaser.com/>
<http://podchaser.com/>
<https://www.podchaser.com>
<http://www.podchaser.com>
<http://podchaser.com>
<https://en.wikipedia.org/wiki/Double_bond>
<https://en.wikipedia.org/wiki/Double_bond/>
<http://en.wikipedia.org/wiki/Double_bond>
<http://en.wikipedia.org/wiki/Double_bond/>
<https://en.wikipedia.org/>
<http://en.wikipedia.org/>
<https://en.wikipedia.org>
<http://en.wikipedia.org>
      }
      MINUS { 
        SELECT distinct ?url WHERE {
          [] schema:isPartOf ?url .
        }
      }
      ?item ?predicate ?url.
      ?property wikibase:directClaim ?predicate.
    }
  }
}

Try it!

Infrastruktur (talk) 16:14, 4 May 2024 (UTC)[reply]

I should note that the above won't list direct claims that match the base URL used for sitelinks either. If this is unwanted, it seems better to ensure that the directClaim BGP is ran first, limiting the worst-case performance of the query. Infrastruktur (talk) 09:05, 5 May 2024 (UTC)[reply]

find persons that are male and female[edit]

(yes I know there are exceptions, but in general this is a glitch.) Hi, I was stumbling across some persons with wrong sex or gender (P21) (incorrectly assigned). As a first step I tried to find people having both sex or gender (P21) using

#title: humans with double sex/gender (non trans)
SELECT ?item  where { #  (count(distinct ?item) as ?count) WHERE { #
  ?item wdt:P21 wd:Q6581072 .
  ?item wdt:P21 wd:Q6581097 .
  #filter not exists {?item wdt:P21/wdt:P279* wd:Q189125 }
  #?item wdt:P31 wd:Q5 .
  #bind (replace(xsd:string(?item),"http://www.wikidata.org/entity/","") as ?itemQid)

  #SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en" } .
} order by ?item offset 0 limit 50

humans with double sex/gender (non trans)

but this leads to timeout in many combinations of parameters tried.

counting also leads to timeout

#title: count humans with double sex/gender (non trans)
SELECT  (count(distinct ?item) as ?count) WHERE { 
  ?item wdt:P21 wd:Q6581072 .
  ?item wdt:P21 wd:Q6581097 .
  filter not exists {?item wdt:P21/wdt:P279* wd:Q189125 }
}

count humans with double sex/gender (non trans)

only when I count the cases without the transgender clause, I get some 934 cases. But not the items themselves.

#title: count humans with double sex/gender
SELECT  (count(distinct ?item) as ?count) WHERE { 
  ?item wdt:P21 wd:Q6581072 .
  ?item wdt:P21 wd:Q6581097 .
}

count humans with double sex/gender

Can somebody help me to re-write the first query to overcome the time constraint?

There are some more questions arising:

How can I, in general, perform such overall quality / consistency checks on large sets (like people)? In most cases I have to tweak queries and only sometimes I will succeed. Is there something with higher privileges and higher timeout thresholds, that can run such consistency checks for all of us?
Is data quality an issue?
And does wikidata scale enough to allow queries on large sets, assuming that the number of items modelling people in wikidata will increase and increase. If not, is quality of data an issue that cannot be pursued any longer and we all will depend on finding incorrect data only by incident?

best --Herzi Pinki (talk) 22:25, 5 May 2024 (UTC)[reply]

SELECT ?item WHERE { ?item wdt:P21 wd:Q6581072, wd:Q6581097 }

Try it!

This works for me in ~25 seconds, resulting in the 934 results. If timeouts are an issue, the slicing service may help you breaking down any amount of results, but it is not a single query any more and you may want to use a script. Here is an example for demonstration purposes; you probably want to work with much larger slices such as WDQS_SLICE_LIMIT=500_000 if necessary. —MisterSynergy (talk) 23:25, 5 May 2024 (UTC)[reply]

works for me. thanks --Herzi Pinki (talk) 19:18, 8 May 2024 (UTC)[reply]

Climate change related items[edit]

Hi everyone. I have a set of nearly 200 Wikidata items and I want to know if there's a chance to make a query to know:

in how many Wikipedias the article exists?
and if they are available on es.wiki, pt.wiki, en.wiki and fr.wiki separately?

Is all this possible? I look forward to your comments. Thank you very much. Paula (WDU) (talk) 16:44, 6 May 2024 (UTC)[reply]

You may try this:

SELECT ?item (COUNT(?wiki) AS ?wikis) ?en ?es ?fr ?pt
WHERE {
  hint:Query hint:optimizer "None".
  VALUES ?item { wd:Q20 wd:Q21 wd:Q22 } # <- Replace with your set of items
  OPTIONAL { ?wiki schema:about ?item; schema:isPartOf / wikibase:wikiGroup "wikipedia". }
  OPTIONAL { ?en_ schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?en. }
  OPTIONAL { ?es_ schema:about ?item; schema:isPartOf <https://es.wikipedia.org/>; schema:name ?es. }
  OPTIONAL { ?fr_ schema:about ?item; schema:isPartOf <https://fr.wikipedia.org/>; schema:name ?fr. }
  OPTIONAL { ?pt_ schema:about ?item; schema:isPartOf <https://pt.wikipedia.org/>; schema:name ?pt. }
}
GROUP BY ?item ?en ?es ?fr ?pt
ORDER BY DESC(?wikis)

Try it!

--Flipping Switches (talk) 04:26, 8 May 2024 (UTC)[reply]

@Flipping Switches, thank you very much! It works. Best, Paula (WDU) (talk) 19:45, 13 May 2024 (UTC)[reply]

IPAA Members with location information[edit]

Hello everyone! I've managed to create a query that will display all members of the Indigenous Performing Arts Alliance who have any of their work location, residence, or place of birth listed as within Canada. However, every time I attempt to reformat the query to get a results column that shows which place within Canada a given member is associated with, I get syntax errors. I am assuming it has to do with the way I am grouping those three properties, but I'm having a hard time figuring out how to resolve it and would appreciate help.

What I have so far:

SELECT DISTINCT ?member ?memberLabel ?memberDescription ?sitelinks WHERE {
  ?member wdt:P463 wd:Q110938829;
  (wdt:P19|wdt:P551|wdt:P937|wdt:P27)/wdt:P131* wd:Q16; #pob/residence/work location
    wikibase:sitelinks ?sitelinks.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC (?sitelinks)

Try it!

MichifDorian (talk) 14:42, 8 May 2024 (UTC)[reply]

Meaning "which place", what columns whith what values do you want to add? Flipping Switches (talk) 18:59, 8 May 2024 (UTC)[reply]

I was initially imagining one "place" column which displayed information from any of the identified properties, but it would probably read more clearly if they were separate columns. So I guess the goal is a query which produces the person's name, and then "place of birth", "work location", and "residence" columns -- any one of which could be blank or could have one or more values. It should only return results that have a value for at least one of those properties, though. MichifDorian (talk) 19:54, 8 May 2024 (UTC)[reply]

Here your initial query (citizenship removed) filters "has at least one of values". Several members are duplicated by different residence which you could GROUP_CONCAT.

SELECT ?member ?memberLabel ?memberDescription ?sitelinks ?birthLabel ?workLabel ?residenceLabel

WITH { SELECT DISTINCT * WHERE {
  ?member wdt:P463 wd:Q110938829;
  (wdt:P19|wdt:P551|wdt:P937)/wdt:P131* wd:Q16.
} } AS %i

WHERE {
  INCLUDE %i.
  OPTIONAL { ?member wdt:P19 ?birth }
  OPTIONAL { ?member wdt:P551 ?residence }
  OPTIONAL { ?member wdt:P937 ?work }
  ?member wikibase:sitelinks ?sitelinks.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC (?sitelinks) ?memberLabel

Try it!

Flipping Switches (talk) 01:26, 9 May 2024 (UTC)[reply]

Finding connections between celebrities[edit]

I am looking to build a query where I can show a list of well-known people (i.e. celebrities, athletes, etc) who are connected to a specific person, and then rank them by fame level (as approximated by the number of wikibase:sitelinks for a person -- although open to any alternate fame approximations).

For example, if I want to list any person who has been on a project or had a professional/personal relationship with Seth Rogen (they worked together, they are friends, they are on the same board, etc), how would I do that? For the output columns, I'd like: 1) Name of celebrity 2) Link to wiki page 3) QID 4) Hometown state or country (if not in US)

Thanks in advance! QueryBeginner (talk) 19:40, 14 May 2024 (UTC)[reply]

Is there a query available to list all the names from this page?[edit]

I am hoping to list all the names on this page as a query output, and rank them by fame, as defined as the number of wikibase:sitelinks: QueryBeginner (talk) 19:53, 14 May 2024 (UTC)[reply]

Help simplifying query: Request all officeholders (p:P1308) and applies to jurisdiction (p:P1001) of governors of a mexican state (wdt:Q17810142)[edit]

Hello community, I have a working query that retrieves the values of 2 properties of a query. It works I just think there's a better way to write this. I'm looking for something along the lines of:

From all items that are subclass wdt:P279 of state governor of mexico wd:Q17810142, give me the values for p:P1001 and p:P1308

SELECT ?item ?itemLabel ?state ?stateLabel
WHERE {
  # from all items that are subclass of state governor of mexico retrieve all items that have property p:P1308 and all items that have property p:1001
  ?wiki_items p:P1308 ?statement. 
  ?wiki_items wdt:P279 wd:Q17810142.
  ?statement ps:P1308 ?item.
  ?wiki_items p:P1001 ?other_statement.
  ?other_statement ps:P1001 ?state.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Basicavisual (talk) 20:18, 14 May 2024 (UTC)[reply]

Football_clubs with stadiums and coordinates[edit]

I am trying to collect football clubs with country/stadium/coordinates. I tried "association football club" - "home venue","country" and "coordinate location" which was translated to this query:

SELECT ?association_football_club ?association_football_clubLabel ?country ?countryLabel ?home_venue ?home_venueLabel ?coordinate_location WHERE {

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 ?association_football_club wdt:P31 wd:Q476028.
 OPTIONAL { ?association_football_club wdt:P17 ?country. }
 OPTIONAL { ?association_football_club wdt:P115 ?home_venue. }
 OPTIONAL { ?association_football_club wdt:P625 ?coordinate_location. }

}

it gives me 39000 football clubs in countries which is great!!!

The goal is to have the name of the stadium with the stadium coordinates. If there is no stadium, the coordinates of the football club might be good so there are at least some coordinates.

But there are lots of problems with missing stadiums and coordinates. Can I change the query to get a complete result list? Marlons Friends (talk) 10:24, 15 May 2024 (UTC)[reply]

That indicates there is data missing in our database which needs to be added, nothing to do with your query. Sjoerd de Bruin (talk) 13:27, 15 May 2024 (UTC)[reply]

Item is part of itself[edit]

I am baffled why this simple entry for a weapon EOC BL 10 inch/45 gun (Q5323780) is regarded as part of itself.

SELECT DISTINCT ?item ?itemLabel  WHERE {
SERVICE wikibase:label {bd:serviceParam wikibase:language "en-GB,en,fr,de,es,pt,pl,nl,cs".}
{
SELECT DISTINCT ?item  WHERE {
    ?item wdt:P361* wd:Q5323780
    }
  }
}

Try it!

Vicarage (talk) 11:17, 16 May 2024 (UTC)[reply]

Wikidata:Request a query

Contents

Entities with articles in many languages but not English.[edit]

Album tracks[edit]

url queries of wikipedia urls take forever.[edit]

find persons that are male and female[edit]

Climate change related items[edit]

IPAA Members with location information[edit]

Finding connections between celebrities[edit]

Is there a query available to list all the names from this page?[edit]

Help simplifying query: Request all officeholders (p:P1308) and applies to jurisdiction (p:P1001) of governors of a mexican state (wdt:Q17810142)[edit]

Football_clubs with stadiums and coordinates[edit]

Item is part of itself[edit]

Navigation menu

Wikidata:Request a query

Entities with articles in many languages but not English.[edit]

Album tracks[edit]

url queries of wikipedia urls take forever.[edit]

find persons that are male and female[edit]

Climate change related items[edit]

IPAA Members with location information[edit]

Finding connections between celebrities[edit]

Is there a query available to list all the names from this page?[edit]

Help simplifying query: Request all officeholders (p:P1308) and applies to jurisdiction (p:P1001) of governors of a mexican state (wdt:Q17810142)[edit]

Football_clubs with stadiums and coordinates[edit]

Item is part of itself[edit]

Navigation menu

Search