Shortcut: WD:RAQ

Wikidata:Request a query

From Wikidata
Jump to: navigation, search

Project
chat

Administrators'
noticeboard

Development
team

Bureaucrats'
noticeboard

Translators'
noticeboard

Requests
for permissions

Requests
for deletions

Property
proposal

Properties
for deletion

Requests
for comment

Partnerships
and imports

Interwiki
conflicts

Request
a query

Bot
requests

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 2017/12.
fishing in the Wikidata river

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

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

Graffiti artists[edit]

Hi, I'm looking for all graffiti sprayers without known identity. So far I used this query to get all graffiti artists (don't know why I get everything in German):

SELECT ?Beruf ?offizieller_Name ?BerufLabel WHERE {

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 ?Beruf wdt:P106 wd:Q15095148.
 OPTIONAL { ?Beruf wdt:P1448 ?offizieller_Name. }
 ?Beruf wdt:P31 wd:Q5.

} Thanks in advance.

Getting info for category Daily Newspaper (Q1110794)[edit]

Hi, I'm using the following query to get information for Daily Newspaper. My questions are: 1) how can I get all articles from following languages in a single query? en,fr,nl,de,it,es 2) how can I get the abstract from each Wikipedia article instead of getting the Wikipedia article URL?

Thanks guys!

SELECT DISTINCT ?newspaperLabel ?newspaperDescription ?countryLabel ?publisherLabel ?site ?logo ?creationdate ?founder ?title ?popLabel ?lowLabel ?award ?article WHERE {
  ?newspaper wdt:P31 wd:Q1110794.
  OPTIONAL { ?newspaper wdt:P495 ?country. }
  OPTIONAL { ?newspaper wdt:P123 ?publisher. }
  OPTIONAL { ?newspaper wdt:P856 ?site. }
  OPTIONAL { ?newspaper wdt:P154 ?logo. }
  OPTIONAL { ?newspaper wdt:P571 ?creationdate. }
  OPTIONAL { ?newspaper wdt:P112 ?founder. }
  OPTIONAL { ?newspaper wdt:P1476 ?title. }
  OPTIONAL { ?newspaper wdt:P291 ?pop. } #place of publication
  OPTIONAL { ?newspaper wdt:P364 ?low. } #language of original work
  OPTIONAL { ?newspaper wdt:P166 ?award. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,nl,de,it,es". }
  Optional { ?article schema:about ?newspaper.
             ?article schema:isPartOf <https://en.wikipedia.org/>. } }
Try it!
 – The preceding unsigned comment was added by Mdeninno (talk • contribs) at 14. 6. 2017, 23:00‎ (UTC).

Find all profiles with P3217 and more values on P569 and also P570[edit]

E.g. Lars Hjortsberg (Q5807838) has two date of birth (P569) values

  • 22 november 1772 - imported from the Wikipedia article
  • 23 november 1772 - created from the mixandmatch import 528

I would like to have a query to see all P3217 related profiles with more values on date of birth (P569) and also for date of death (P570) and display

  • value
  • if date the calendar used
  • ranking
  • ref

as the Wikipedia import is the same as the article and the P3217 is from a professional researched source by Swedish National Archives we get profiles that need to be checked in sv:Wikipedia

Thanks I get lost on the p and ps and..... - Salgo60 (talk) 15:27, 1 December 2017 (UTC)

The following query uses these:

  • Properties: Dictionary of Swedish National Biography (P3217) View with Reasonator View with SQID, Wikimedia language code (P424) View with Reasonator View with SQID, date of birth (P569) View with Reasonator View with SQID, reference URL (P854) View with Reasonator View with SQID, stated in (P248) View with Reasonator View with SQID, imported from (P143) View with Reasonator View with SQID
     1 SELECT ?item ?date1 ?p1 ?c1 ?r1 ?refurl1 ?angesi1Label ?i1 ?date2 ?p2 ?c2 ?r2 ?refurl2 ?angesi2Label ?i2 WHERE
     2 {
     3   ?item wdt:P3217 ?sbl .
     4   ?item p:P569 ?date1stm .
     5   ?date1stm psv:P569 ?date1node .
     6   ?date1node wikibase:timeValue ?date1 .
     7   ?date1node wikibase:timePrecision ?p1 .
     8   ?date1node wikibase:timeCalendarModel ?cal1 .
     9   BIND(IF(?cal1=wd:Q1985727,'G',IF(?cal1= wd:Q1985786, 'J', '?')) AS ?c1) 
    10   ?date1stm wikibase:rank ?rank1 .
    11   BIND(IF(?rank1=wikibase:PreferredRank,'P',IF(?rank1= wikibase:NormalRank, 'N', 'D')) AS ?r1) 
    12   OPTIONAL { ?date1stm prov:wasDerivedFrom ?ref1.
    13      OPTIONAL { ?ref1 pr:P854 ?refurl1 . }
    14      OPTIONAL { ?ref1 pr:P248 ?angesi1 . }
    15      OPTIONAL { ?ref1 pr:P143/wdt:P424 ?i1 . }
    16   }
    17   ?item p:P569 ?date2stm .
    18   ?date2stm psv:P569 ?date2node .
    19   ?date2node wikibase:timeValue ?date2 . 
    20   ?date2node wikibase:timePrecision ?p2 .
    21   ?date2node wikibase:timeCalendarModel ?cal2 .
    22   BIND(IF(?cal2=wd:Q1985727,'G',IF(?cal2= wd:Q1985786, 'J', '?')) AS ?c2) 
    23   ?date2stm wikibase:rank ?rank2 .
    24   BIND(IF(?rank2=wikibase:PreferredRank,'P',IF(?rank2= wikibase:NormalRank, 'N', 'D')) AS ?r2)
    25   OPTIONAL { ?date2stm prov:wasDerivedFrom ?ref2 .
    26      OPTIONAL { ?ref2 pr:P854 ?refurl2 . }
    27      OPTIONAL { ?ref2 pr:P248 ?angesi2 . }
    28      OPTIONAL { ?ref2 pr:P143/wdt:P424 ?i2 . }
    29   }
    30   FILTER(?date1<?date2)
    31   SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
    32 }
    33 ORDER BY ?item ?refurl1 ?refurl2
    
I added columns for timePrecision (?p) and importedFrom (?i). Change P569 to P570 (in 4 places) to get death dates instead of birth dates.
--Larske (talk) 22:46, 7 December 2017 (UTC)

Extracting first letter of a name (and group it by frequency)[edit]

Hello! I'm trying to extract the first letter of a string, like for example, in this list of sovereign states presidents:

The following query uses these:

  • Properties: instance of (P31) View with Reasonator View with SQID, head of government (P6) View with Reasonator View with SQID
    1 SELECT ?Estatu_subiranoaLabel ?agintariaLabel WHERE {
    2   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    3   ?Estatu_subiranoa wdt:P31 wd:Q3624078.
    4   ?Estatu_subiranoa wdt:P6 ?agintaria
    5 }
    

But I can't find a way to GREP the result in the documentation. Could someone help? -Theklan (talk) 10:58, 2 December 2017 (UTC)

The following query uses these:

  • Properties: instance of (P31) View with Reasonator View with SQID, head of government (P6) View with Reasonator View with SQID
     1 SELECT ?first_letter (count(?first_letter) as ?count) WHERE {
     2   ?Estatu_subiranoa wdt:P31 wd:Q3624078;
     3                  rdfs:label ?Estatu_subiranoaLabel;
     4                  wdt:P6 ?agintaria.
     5   ?agintaria rdfs:label ?agintariaLabel.
     6   FILTER(lang(?Estatu_subiranoaLabel)="en" && lang(?agintariaLabel)="en") 
     7   bind( substr( ?agintariaLabel, 1, 1 ) as ?first_letter )
     8 }
     9 GROUP BY ?first_letter
    10 ORDER by DESC(?count)
    

If this is what you are looking for. John Samuel 11:15, 2 December 2017 (UTC)

Exactly that, thanks @Jsamwrites:! -Theklan (talk) 21:42, 5 December 2017 (UTC)

French communes and their liberation date[edit]

Hello everyone, is there a history property so that one can query all 36000 french communes and know their liberation from Nazis date (be it empty or input) ? Or any query showing liberation date + town name 1944-1945 ? Thanks ! --Bouzinac (talk) 16:03, 4 December 2017 (UTC)

I checked a few of them and it seems we don't have this data yet. The property for this could be significant event (P793): liberation from occupation with point in time (P585). If you have a dataset for this, it should be easy to import and then query for it. Matěj Suchánek (talk) 17:49, 4 December 2017 (UTC)
Would this list Chronologie de la Libération en France be of any help ? The most noticeable liberations would be Ajaccio 09 /09/1943 ; Bayeux 07 /06/1944 ; Paris 25 / 08 /1944 ; Saint Nazaire 11 / 05 / 1945 among many others--Bouzinac (talk) 20:28, 4 December 2017 (UTC)

More than one value[edit]

Hello. I want to find all items that have instance of (P31) with national association football team (Q6979593) and have more than 1 value with competition class (P2094). Xaris333 (talk) 22:09, 4 December 2017 (UTC)

SELECT ?item ?cc1 ?cc2 WHERE {
  ?item wdt:P31 wd:Q6979593 .
  ?item wdt:P2094 ?cc1 .
  ?item wdt:P2094 ?cc2 .
  FILTER(?cc1 != ?cc2) .
}

Try it! There are none. —MisterSynergy (talk) 22:18, 4 December 2017 (UTC)

Thanks! Xaris333 (talk) 19:23, 5 December 2017 (UTC)

List of all properties that are instance of (P31) property likely to be challenged (Q44597997) along with the usage count of every one of the properties[edit]

For the project of a Living People policy, I want to have a Listeria list of properties that are instance of (P31) property likely to be challenged (Q44597997). Can someone write me an appropriate Query that I can copy into Listeria? ChristianKl () 20:23, 5 December 2017 (UTC)

{{Wikidata list|sparql=SELECT ?item ?property ?wikilink (COUNT(?p) AS ?cnt) WHERE {
  wd:Q44597997 ^wdt:P31 [ wdt:P1629 ?item ] .
  ?item wdt:P1687 ?property .
  BIND(CONCAT('[[Property:', SUBSTR(STR(?property), 32), ']]') AS ?wikilink) .
  ?property wikibase:claim ?p .
  [] ?p [] .
} GROUP BY ?item ?property ?wikilink
|columns=?item:Item,?wikilink:Property,?cnt:Number of claims|links=all|autolist=fallback}}
{{Wikidata list end}}

Not a beauty, but Listeria’s inability to deal with entities other than items makes such hacks necessary. —MisterSynergy (talk) 21:16, 5 December 2017 (UTC)

Thanks. ChristianKl () 23:37, 5 December 2017 (UTC)

Find Items with number of statements![edit]

At WIKIDATACON, it was mentioned that eventually items could be found, based on quality! But, until then;

Is there a way to find/list/query items with the number of statements it contains?

and, items with no labels in a particular language? or items with only one label, with no additional languages?

and, items that have no "instance of and subclass of" statements? (without using the game!)

and, items with no external identifiers?

If it has already been answered!? I apologise for asking again!  – The preceding unsigned comment was added by Wallacegromit1 (talk • contribs) at 10:15, 7 December 2017‎ (UTC).

Yes all of that is technically possible with the Query Service—but it only works if you restrict it to certain topics (otherwise the queries would time out). So could you please ask a more specific question? —MisterSynergy (talk) 09:22, 7 December 2017 (UTC)


Thanks for the swift reply!

More specific questions would be;

Query:

1. items with only 1(one) or 2 statements (example: only Commons Category, Or, Commons Category and Topics Main Category)?

2. items with labels only in one language(en)?

3. items with only Freebase external identifiers?

items with exactly two statements
SELECT DISTINCT ?item ?itemLabel
WHERE{
  ?item wikibase:statements 2 .
  FILTER ( ?item not in ( wd:Q16943273,wd:Q17566023 ) ) #Exclude item of wikidata tour
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
} 
Limit 100

Try it!  – The preceding unsigned comment was added by Bigbossfarin (talk • contribs) at 12:41, 7 December 2017‎ (UTC).

Items with no other statements except the Freebase ID (P646):
SELECT ?item WHERE {
  ?item wdt:P646 ?freebaseId;
        wikibase:statements "1"^^xsd:integer.
}
Try it! --TweetsFactsAndQueries (talk) 12:46, 7 December 2017 (UTC)

I think (2.) is not possible, this one is similar:

items with English but no French label
SELECT ?item ?label_en WHERE {
  ?item rdfs:label ?label_en filter(lang(?label_en) = "en") .
  MINUS { ?item rdfs:label ?label_fr filter(lang(?label_fr) = "fr") . }
}
LIMIT 100

Try it! Bigbossfarin (talk) 13:02, 7 December 2017 (UTC)

items without instance of and subclass of
SELECT ?item WHERE {
?item wikibase:sitelinks [] .
MINUS { ?item (wdt:P31|wdt:P279) [] } .
}
LIMIT 1000

Try it!Bigbossfarin (talk) 13:12, 7 December 2017 (UTC)


Thanks a lot, learned a lot of new syntax!

With out a qualifier[edit]

Hello. I need:

Xaris333 (talk) 09:51, 8 December 2017 (UTC)

  1. SELECT ?item ?value WHERE {
      ?item p:P1352 ?s .
      ?s ps:P1352 ?value; pq:P447 wd:Q253414 .
      MINUS { ?s pq:P459 [] }
    }
    
    Try it!
  2. SELECT ?item ?value ?time WHERE {
      ?item p:P1352 ?s .
      ?s ps:P1352 ?value; pq:P447 wd:Q253414; pqv:P585 [ wikibase:timeValue ?time ] .
      MINUS { ?s pq:P1358 [] }
      FILTER(?time > "+2011-08-23T00:00:00Z"^^xsd:dateTime) .  
    }
    
    Try it!

MisterSynergy (talk) 11:08, 8 December 2017 (UTC)


Thanks! Xaris333 (talk) 14:31, 8 December 2017 (UTC)

Article title and label[edit]

Hello. I need a query that finds items with instance of (P31) --> national association football team (Q6979593) and the English Wikipedia article has different title from the English label. Is that possible? Xaris333 (talk) 00:19, 9 December 2017 (UTC)

SELECT ?item ?label ?name ?sitelink WHERE {
  ?item wdt:P31 wd:Q6979593 .
  ?item rdfs:label ?label .
  FILTER(LANG(?label) = 'en') .
  ?sitelink schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?name .
  FILTER(?label != ?name) .
}

Try it!MisterSynergy (talk) 06:19, 9 December 2017 (UTC)

Thanks. Xaris333 (talk) 02:49, 10 December 2017 (UTC)

Items that got this reward: Kasper Salin Prize (Q3432243)[edit]

I would like to create a query that lists all items that has gotten the Kasper Salin Prize (Q3432243) award, preferably sorted by date, since one winner will be awarded each year since 1960's. How to do this?  – The preceding unsigned comment was added by Mippzon (talk • contribs) at 19:04, 10 December 2017‎ (UTC).

SELECT ?item ?itemLabel ?year WHERE {
  ?item p:P166 ?s .
  ?s ps:P166 wd:Q3432243 .
  OPTIONAL { ?s pq:P585 ?time . BIND(YEAR(?time) AS ?year) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ASC(?year)
Try it! Only six results. Guess you need to add the data first (use award received (P166) with qualifiers point in time (P585)). —MisterSynergy (talk) 18:11, 10 December 2017 (UTC)
Wow, thanks for swift reply (and for adding my signature, since I forgot that). Exactly what I needed! //Mippzon (talk) 18:15, 10 December 2017 (UTC)
Would be nice to show the image of the objects as well, but I can't get this to work with the query helper. The column appears, but no image, even if image exists in item? //Mippzon (talk) 18:23, 10 December 2017 (UTC)
Not sure whether the query service can display images at all. It would probably be better to make a Listeria list for that, once the data set is complete. —MisterSynergy (talk) 18:33, 10 December 2017 (UTC)
It is possible:
#defaultView:ImageGrid
SELECT ?item ?itemLabel ?year ?img WHERE {
  ?item p:P166 ?s .
  ?s ps:P166 wd:Q3432243 .
  OPTIONAL { ?s pq:P585 ?time . BIND(YEAR(?time) AS ?year) } .
  OPTIONAL { ?item wdt:P18 ?img } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ASC(?year)
Try it!
See Wikidata:SPARQL query service/Wikidata Query Help/Result Views. Matěj Suchánek (talk) 18:55, 10 December 2017 (UTC)
Thanks! I was actually going for Listeria. I've done one attempt here but I can't get the years right. I want my Listeria to look something like this. Any tips? //Mippzon (talk) 21:34, 10 December 2017 (UTC)

Duplicate rows in result set?[edit]

In this simple hack of a Wikidata weekly summary report, please explain why I'm getting what look like duplicate rows in the result set - e.g. for Gandhi (Q202211). I know I could use SELECT DISTINCT; but I don't know why some items result in dupes whilst others do not. I'm not seeing the proliferation of values in ?film ?filmLabel ?statusLabel that woud lead to the pattern of dupes I see.

Also, why does adding GROUP BY ?film to the foot of the query break it?

SELECT ?film ?filmLabel ?statusLabel WHERE {
  ?film wdt:P31 wd:Q11424 ;
            wdt:P577 ?date .
            OPTIONAL {?film wdt:P1552 ?status .} 
  FILTER("1983-01-01"^^xsd:dateTime <= ?date && ?date < "1984-01-01"^^xsd:dateTime).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!

thx --Tagishsimon (talk)

The reason for having two result rows for Gandhi (Q202211) is that this film has three values for publication date (P577) of which two dates pass you date filter.
SELECT ?film ?filmLabel ?date ?statusLabel WHERE {
  ?film wdt:P31 wd:Q11424 ;
            wdt:P577 ?date .
            OPTIONAL {?film wdt:P1552 ?status .} 
  FILTER(?film=wd:Q202211)
  FILTER("1983-01-01"^^xsd:dateTime <= ?date && ?date < "1984-01-01"^^xsd:dateTime).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!
--Larske (talk) 01:56, 12 December 2017 (UTC)
Thanks. Completely overlooked P577. --Tagishsimon (talk) 02:00, 12 December 2017 (UTC)

Disasters starting on Friday 13[edit]

The main issue here is how to infer the day of the week. Here's what I got so far:

The following query uses these:

  • Properties: instance of (P31) View with Reasonator View with SQID, subclass of (P279) View with Reasonator View with SQID, start time (P580) View with Reasonator View with SQID
    1 SELECT ?disaster ?disasterLabel WHERE {
    2   ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
    3             wdt:P580 ?start_time .
    4   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    5 }
    

Now how to go about finding those that happened on a Friday? --Daniel Mietchen (talk) 02:40, 15 December 2017 (UTC)

We do have items like 13 October 2017 (Q37788009). If all of them had point in time (P585) and somehow indicated the day of week, this would be piece of cake. Still, the day of week can be computed (I used w:cs:Algoritmus pro výpočet dne v týdnu#Zellerův algoritmus):
# using https://cs.wikipedia.org/wiki/Algoritmus_pro_v%C3%BDpo%C4%8Det_dne_v_t%C3%BDdnu#Zeller%C5%AFv_algoritmus
SELECT ?disaster ?disasterLabel ?start_time WHERE {
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            wdt:P580 ?start_time .
  BIND( DAY( ?start_time ) AS ?q ) .
  FILTER( ?q = 13 ) .
  BIND( MONTH( ?start_time ) + IF( MONTH( ?start_time ) < 3, 12, 0 ) AS ?m ) .
  BIND( YEAR( ?start_time ) - IF( MONTH( ?start_time ) < 3, 1, 0 ) AS ?_year ) .
  BIND( ?_year - FLOOR( ?_year / 100 ) * 100 AS ?K ) . # modulo
  BIND( FLOOR( YEAR( ?start_time ) / 100 ) AS ?J ) .
  BIND( ?q + FLOOR( 13 * ( ?m + 1 ) / 5 ) + ?K + FLOOR( ?K / 4 ) + FLOOR( ?J / 4 ) - 2 * ?J AS ?_h ) .
  FILTER( ?_h - FLOOR( ?_h / 7 ) * 7 = 6 ) . # modulo, 0 is Saturday
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } .
}
Try it!
Matěj Suchánek (talk) 09:05, 15 December 2017 (UTC)

Tuesday 26 is the date...[edit]

...with the fewest disaster starts. Just out of curiosity I modified the question above to present the frequency of disaster starts for each date/weekday combination. Based on the 1,556 disasters with a starting date given with a precision on date level, it seems that the most frequent date is the 25th with 68 disaster starts, 13th only have 59, and the most frequent weekday is Wednesday with 235 disaster starts, Friday only have 219. However, the most frequent date/weekday combination is not Wednesday 25, it is a tie between Tuesday 22 and Saturday 25, both with 15 disaster starts. Friday 13 does only count for 10 disaster starts. Tuesday 26 seems to be the "safest" with just one disaster start.

# using https://cs.wikipedia.org/wiki/Algoritmus_pro_v%C3%BDpo%C4%8Det_dne_v_t%C3%BDdnu#Zeller%C5%AFv_algoritmus
SELECT (?q AS ?Date) (SUM(?mon) AS ?Mon) (SUM(?tue) AS ?Tue) (SUM(?wed) AS ?Wed) (SUM(?thu) AS ?Thu) (SUM(?fri) AS ?Fri) (SUM(?sat) AS ?Sat) (SUM(?sun) AS ?Sun)  (COUNT(?start_time) AS ?Tot)  WHERE {
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            p:P580 ?start_time_stm .
  ?start_time_stm ps:P580 ?start_time .
  ?start_time_stm psv:P580 ?start_time_node .
  ?start_time_node wikibase:timePrecision ?tp . FILTER(?tp=11)
  BIND( DAY( ?start_time ) AS ?q ) .
  BIND( MONTH( ?start_time ) + IF( MONTH( ?start_time ) < 3, 12, 0 ) AS ?m ) .
  BIND( YEAR( ?start_time ) - IF( MONTH( ?start_time ) < 3, 1, 0 ) AS ?_year ) .
  BIND( ?_year - FLOOR( ?_year / 100 ) * 100 AS ?K ) . # modulo
  BIND( FLOOR( YEAR( ?start_time ) / 100 ) AS ?J ) .
  BIND( ?q + FLOOR( 13 * ( ?m + 1 ) / 5 ) + ?K + FLOOR( ?K / 4 ) + FLOOR( ?J / 4 ) - 2 * ?J AS ?_h ) .
  BIND( ?_h - FLOOR( ?_h / 7 ) * 7 AS ?weekday) . # modulo, 0 is Saturday
  BIND(IF(?weekday=2,1,0) AS ?mon)
  BIND(IF(?weekday=3,1,0) AS ?tue)
  BIND(IF(?weekday=4,1,0) AS ?wed)
  BIND(IF(?weekday=5,1,0) AS ?thu)
  BIND(IF(?weekday=6,1,0) AS ?fri)
  BIND(IF(?weekday=0,1,0) AS ?sat)
  BIND(IF(?weekday=1,1,0) AS ?sun)
}
GROUP BY ?q
ORDER BY ?Date

Try it!

--Larske (talk) 14:37, 15 December 2017 (UTC)

@Larske, Matěj Suchánek, Daniel Mietchen: - these are awesome, but it occurred to me that a lot of events use point in time (P585) not start time (P580), especially for things like earthquakes or air crashes - there are 3600 with P585 and 1637 with P580. Putting these into the script gives us a tie between Mon 12th and Tue 22nd for most common (38 events). The least common is Sun 17th, Wed 17th, or Mon 30th, all with 12. Friday 13th has 24, very close to the average of 22.6. Andrew Gray (talk) 17:01, 15 December 2017 (UTC)
# using https://cs.wikipedia.org/wiki/Algoritmus_pro_v%C3%BDpo%C4%8Det_dne_v_t%C3%BDdnu#Zeller%C5%AFv_algoritmus
SELECT (?q AS ?Date) (SUM(?mon) AS ?Mon) (SUM(?tue) AS ?Tue) (SUM(?wed) AS ?Wed) (SUM(?thu) AS ?Thu) (SUM(?fri) AS ?Fri) (SUM(?sat) AS ?Sat) (SUM(?sun) AS ?Sun)  (COUNT(?start_time) AS ?Tot)  WHERE {
 { 
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            p:P580 ?start_time_stm .
  ?start_time_stm ps:P580 ?start_time .
  ?start_time_stm psv:P580 ?start_time_node .
  ?start_time_node wikibase:timePrecision ?tp . FILTER(?tp=11) 
 }
 UNION # to cover both "point in time" and "start time" events
 {
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            p:P585 ?start_time_stm .
  ?start_time_stm ps:P585 ?start_time .
  ?start_time_stm psv:P585 ?start_time_node .
  ?start_time_node wikibase:timePrecision ?tp . FILTER(?tp=11) 
 }
  BIND( DAY( ?start_time ) AS ?q ) .
  BIND( MONTH( ?start_time ) + IF( MONTH( ?start_time ) < 3, 12, 0 ) AS ?m ) .
  BIND( YEAR( ?start_time ) - IF( MONTH( ?start_time ) < 3, 1, 0 ) AS ?_year ) .
  BIND( ?_year - FLOOR( ?_year / 100 ) * 100 AS ?K ) . # modulo
  BIND( FLOOR( YEAR( ?start_time ) / 100 ) AS ?J ) .
  BIND( ?q + FLOOR( 13 * ( ?m + 1 ) / 5 ) + ?K + FLOOR( ?K / 4 ) + FLOOR( ?J / 4 ) - 2 * ?J AS ?_h ) .
  BIND( ?_h - FLOOR( ?_h / 7 ) * 7 AS ?weekday) . # modulo, 0 is Saturday
  BIND(IF(?weekday=2,1,0) AS ?mon)
  BIND(IF(?weekday=3,1,0) AS ?tue)
  BIND(IF(?weekday=4,1,0) AS ?wed)
  BIND(IF(?weekday=5,1,0) AS ?thu)
  BIND(IF(?weekday=6,1,0) AS ?fri)
  BIND(IF(?weekday=0,1,0) AS ?sat)
  BIND(IF(?weekday=1,1,0) AS ?sun)
}
GROUP BY ?q
ORDER BY ?Date

Try it!

Oh, so this Sunday will be a safe day :)
--- Jura 17:15, 15 December 2017 (UTC)
As someone with a birthday on the 17th of a month, I am certainly happy with these results :-) Andrew Gray (talk) 19:51, 15 December 2017 (UTC)
Looks like the minimum for the 17th is lower than for the 31st (overall lowest) :)
--- Jura 20:34, 15 December 2017 (UTC)
Comparing dates greater than 28th is not completely fair as they are not as frequent as dates 1st–28th. In a year are only 7 days with date 31, 11 with date 30, and 11 (or 12 for leap years) with date 29 compared to 12 days with date 1, 2, 3, ..., 28. So the date 31st is in fact the most "risky" when it comes to disasters. --Larske (talk) 09:14, 16 December 2017 (UTC)
Maybe it's the least risky as so many already happened that day ;)
BTW, I created Wikidata:WikiProject Calendar Dates.
--- Jura 09:22, 16 December 2017 (UTC)
@Larske, Matěj Suchánek, Andrew Gray, Jura1: I am enjoying both the queries themselves, which taught me some new bits of SPARQL, and the results, which provide for great starting points to poke around in history. Thanks! --Daniel Mietchen (talk) 01:58, 17 December 2017 (UTC)

Source[edit]

Hello. I need a query that finds items with ranking (P1352) with any value and with score by (P447) --> FIFA (Q253414) as a qualifier, but the source has no reference URL (P854). Xaris333 (talk) 10:39, 16 December 2017 (UTC)

SELECT ?item ?itemLabel ?rankingValue ?pointInTime WHERE {
  ?item p:P1352 ?s .
  ?s ps:P1352 ?rankingValue; pq:P447 wd:Q253414 .
  OPTIONAL { ?s pq:P585 ?pointInTime }
  MINUS { ?s prov:wasDerivedFrom [ pr:P854 [] ] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],en' }
}

Try it!MisterSynergy (talk) 10:47, 16 December 2017 (UTC)

Thanks. Xaris333 (talk) 10:50, 16 December 2017 (UTC)