User:Deansfa

to explore:

fr-N	Cet utilisateur a pour langue maternelle le français.

en-N	This user has a native understanding of English.

de-1	Dieser Benutzer beherrscht Deutsch auf grundlegendem Niveau.

nl-0	Deze gebruiker heeft geen kennis van het Nederlands (of begrijpt het met grote moeite).

es-0	Este usuario no tiene ningún conocimiento del español (o lo entiende con mucha dificultad).

pt-0	Este utilizador não compreende português (ou compreende com dificuldades consideráveis).

sv-0	Den här användaren har inga kunskaper i svenska (eller förstår det bara med en stor svårighet).

ru-0	Этот участник не владеет русским языком (или понимает его с трудом).

mul-0	This user has no knowledge of multiple languages (or understands it with considerable difficulty).

This user loves Wikidata.

This user uses QuickStatements.

500,000+

This user has made over 500,000 contributions to Wikidata.

This user lives in the United States.
Cet utilisateur vit aux États-Unis.

MyQ

instance of

	human	edit

	0 references add reference

add value

place of birth

	7th arrondissement of Lyon	edit

	0 references add reference

add value

sex or gender

	male	edit

	0 references add reference

add value

country of citizenship

	France	edit

	0 references add reference

	United States of America	edit

	0 references add reference

add value

occupation

	data engineer	edit

	0 references add reference

add value

residence

	Quaker Ridge	edit

	0 references add reference

	200 East 90th Street	edit

	0 references add reference

	Astor Terrace	edit

	0 references add reference

add value

Projects

Press/References: Importing articles and their authors (WSJ journalists. WSJ articles, NYT, Bloomberg, ...). I'm using "Cite Q" to source articles with references in WD.
Past or future projects:
- talk show series episodes:
  - Frédéric Taddeï (Q603) TV talk shows:
    - liste des épisodes de Ce soir ou jamais (done but partial, got the data from an old version of the article),
    - liste des épisodes d'Interdit d'interdire (done),
    - liste des épisodes des Visiteurs du soir (ongoing)
  - Bill Maher (Q489) TV talk shows:
    - liste des épisodes de real time with bill maher (done)
- Érudit platform:
  - Revue musicale OICRM (Q47523513) articles (done) [3]
- National Bureau of Economic Research Working Paper Series (Q57081850) articles (done) [4]
  - TODO: remove Q1173810 from articles
- backfilling empty items for:
  - ht.wikipedia.org Example ; maybe with a mix of scraping + parsing (current)
  - vi.wikipedia.org (upcoming)
- future: importing all nyc buildings + protected neighborhoods (pluto dataset + nyc lpc historic neighborhood dataset)

Tracked items

Roy Purdy: Q50574463 -> Q61493249
Tana Mongeau: Q55413284
Margaret Talev: Q59781428
Fédération internationale de descente: Q63252743
Michael Le: Q96251580
Else: Q98973790
Pas Sages: Q99372657
My (Мы): Q48955194
Ubici (Убийцы): Q59149260
La Belle Vie: Q100977064
Rory Farquharson: Q104254991
Inez Stepman: Q104761974
Деревянные киты: Q105329870, Edgär: Q105329824, Порез на Собаке: Q105329732
Нюдовый Чёс: Q106267193
Guillaume Nicoulaud: Q106641831
Laurent Lufroy: Q107012664
Cabaret Nocturne: Q107399498
Wilfried Demaret/BB27000: Q107426453
Thomas Barrandon: Q109478714
Alexandra Hunt: Q110972245, Mariahlynn Q110972517
Mélodie Perrault: Q112057607
Ритуальные Услуги:
Sophie Araque-Liu: Q113536901
Pauline Quillon: Q113558671
Pastel Ghost: Q116091969
Gwendoline: Q116368208
Beate Karlsson: Q122852870
Candeur Cyclone: Q123575091
Lizzy Musi: Q129175288
Agustina Vergara Cid Q130316097

Some SPARQL stuff of my own

Articles that are "Featured", "Good" or "Features list" in one language and don't exist in an other

# Featured/Good articles in Korean that don't have a French Wikipedia page

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?featuredArticle schema:about ?item ; 
                   schema:inLanguage "ko" ; 
                   wikibase:badge ?badgeValues .

  VALUES ?badgeValues { 
    wd:Q17437796 # that are featured articles
  	wd:Q17506997 # or featured lists
    wd:Q17437798 # or good articles
  } 

  OPTIONAL { 
    ?sitelink schema:about ?item ; 
              schema:inLanguage "fr" .
  } 
  FILTER (!BOUND(?sitelink))

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,ja,ko" .
  }
}
ORDER BY ?itemLabel

Try it!

Horse breeds that don't have an article in French

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31 wd:Q3745054 . 
  OPTIONAL { 
    ?sitelink schema:about ?item ; 
              schema:inLanguage "fr" .
  } 
  FILTER (!BOUND(?sitelink))

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,de" .
  }
}
ORDER BY ?itemLabel

Try it!

People born in 1899 in Polotsk

# People born in 1899 in Polotsk

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  
  ?item wdt:P19 wd:Q200797 ; 
        wdt:P569 ?date .
  FILTER (YEAR(?date) = 1899)
 
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,ru" .
  }
}
ORDER BY ?itemLabel

Try it!

Poems by Arthur Rimbaud

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31 wd:Q5185279 ;
        wdt:P50 wd:Q493 . 

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr" .
  }
}
ORDER BY ?itemLabel

Try it!

Poems that has Ophélie in their label

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31 wd:Q5185279 ;
        rdfs:label ?label .

  FILTER(LANG(?label) IN ("en", "es", "fr", "de")) . 
  FILTER(CONTAINS(?label, "Ophélie")) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,de,es" .
  }
}
ORDER BY ?itemLabel

Try it!

Velodromes in the United States

SELECT DISTINCT ?item ?itemLabel 
WHERE {
  ?item wdt:P31 wd:Q830528 ;
        wdt:P17 wd:Q30 . 

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr" .
  }
}
ORDER BY ?itemLabel

Try it!

#Locations of velodromes
#defaultView:Map
SELECT ?objectLabel ?objectDescription ?link ?coord
WHERE {
  ?object wdt:P31 wd:Q830528 ; wdt:P625 ?coord .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en"
  }
}

Try it!

Architecture

#Buildings on Park Avenue
#defaultView:Map
SELECT ?objectLabel ?objectDescription ?link ?coord
WHERE {
  ?object wdt:P669 wd:Q109711 ; wdt:P625 ?coord .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en"
  }
}

Try it!

#Buildings with Park Avenue in their label that don't have the property "voie" set 
SELECT DISTINCT ?objectLabel ?objectDescription ?voieLabel
WHERE {
  ?object wdt:P31 wd:Q13402009 ; rdfs:label ?label .
  OPTIONAL { ?object wdt:P669 ?voie }
  FILTER(CONTAINS(?label, "Park Avenue")) .
  FILTER (!BOUND(?voie)) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr"
  }
}

Try it!

#a little of a repeat of the previous one.
#apartment buildings in the USA that don't have the property "voie" set 
SELECT DISTINCT ?objectLabel ?objectDescription ?voieLabel
WHERE {
  ?object wdt:P17 wd:Q30 ; wdt:P31 wd:Q13402009 ; rdfs:label ?label .
  OPTIONAL { ?object wdt:P669 ?voie }
  #FILTER(CONTAINS(?label, "89th Street")) .
  FILTER (!BOUND(?voie)) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en"
  }
}

Try it!

#Buildings on Park Avenue that don't have a street number 
SELECT DISTINCT ?object ?objectLabel ?voieLabel ?streetNumber
WHERE {
  ?object wdt:P669 wd:Q109711 . 
  ?object p:P669 ?voie . 
  OPTIONAL { ?voie pq:P670 ?streetNumber } .
  FILTER (!BOUND(?streetNumber)) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr"
  }
}

Try it!

#Apartment buildings in Manhattan that don't have coordonates 
SELECT ?object ?objectLabel $objectDescription $coord
WHERE {
  ?object wdt:P31 wd:Q13402009 .
  ?object wdt:P131 wd:Q11299 .
  OPTIONAL { ?object wdt:P625 ?coord } .
  FILTER (!BOUND(?coord)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}

Try it!

# REGEX usage example:
# buildings in Manhattan that have "Drive", "Avenue" or "Street" in their label
SELECT DISTINCT ?object ?objectLabel ?voieLabel ?streetNumber
  WHERE {
    ?object wdt:P131 wd:Q11299 ; rdfs:label ?label .
    ?object p:P669 ?voie.
    OPTIONAL { ?voie pq:P670 ?streetNumber. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
    FILTER REGEX(STR(?label), "Drive|Avenue|Street")
    FILTER(!BOUND(?streetNumber))
  }

Try it!

# churches taller than 100 meters in France
SELECT $item $itemLabel ?elevation ?pic WHERE { 
  $item (wdt:P31/(wdt:P279*)) wd:Q16970 ; wdt:P17 wd:Q142 .
  ?item p:P2048 ?st .
  ?st psn:P2048 $height .
  
  ?height wikibase:quantityAmount ?elevation . FILTER(?elevation > 100) .
  ?height wikibase:quantityUnit wd:Q11573 .
  
  OPTIONAL { ?item wdt:P18 ?pic . }
      
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}
GROUP BY $item $itemLabel ?elevation ?pic

Try it!

Interdit d'interdire

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

#List of episodes with aggregated comma-separated guests
SELECT DISTINCT ?episode ?numero_episode ?date ?episodeLabel (GROUP_CONCAT(DISTINCT ?guestLabel; SEPARATOR = ", ") AS ?liste_invites) (URI(CONCAT("https://www.youtube.com/watch?v=", ?youtube)) AS ?lien_youtube)
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?episode wdt:P577 ?date.
  ?statement ps:P179 wd:Q56816469.
  ?statement pq:P1545 ?numero_episode.
  OPTIONAL { ?episode wdt:P1651 ?youtube. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?episode rdfs:label ?episodeLabel.
  }
}
GROUP BY ?episode ?numero_episode ?date ?episodeLabel ?youtube
ORDER BY DESC(xsd:integer(?numero_episode))

Try it!

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

#List of episodes with episode number per season and aggregated comma-separated guests
SELECT DISTINCT ?episode ?no_episode ?no_episode_saison ?date ?episodeLabel (GROUP_CONCAT(DISTINCT ?guestLabel; SEPARATOR = ", ") AS ?liste_invites) (URI(CONCAT("https://www.youtube.com/watch?v=", ?youtube)) AS ?lien_youtube)
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  ?episode p:P4908 ?season.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?episode wdt:P577 ?date.
  ?statement ps:P179 wd:Q56816469.
  ?statement pq:P1545 ?no_episode.
  ?season pq:P1545 ?no_episode_saison.
  OPTIONAL { ?episode wdt:P1651 ?youtube. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?episode rdfs:label ?episodeLabel.
  }
}
GROUP BY ?episode ?no_episode ?no_episode_saison ?date ?episodeLabel ?youtube
ORDER BY DESC(xsd:integer(?no_episode))

Try it!

#List of guests per number of appearance
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT (COUNT (?guest) as ?count) ?guestLabel 
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?statement ps:P179 wd:Q56816469.
  ?statement pq:P1545 ?numero_episode.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en".
                         ?guest rdfs:label ?guestLabel.}
}
GROUP BY (?guestLabel)
ORDER BY DESC (?count)

Try it!

SELECT DISTINCT ?episode ?guestLabel ?genderLabel ?numero_episode ?occupationLabel
WHERE {
  ?episode (wdt:P31/(wdt:P279*)) wd:Q1983062;
    p:P179 ?statement;
    wdt:P5030 ?guest.
  ?statement ps:P179 wd:Q56816469;
    pq:P1545 ?numero_episode.
  ?guest wdt:P21 ?gender.
  OPTIONAL { ?guest wdt:P106 ?occupation. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?gender rdfs:label ?genderLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
ORDER BY ASC(xsd:integer(?numero_episode))

Try it!

#Parité homme/femme dans Interdit d'interdire

SELECT DISTINCT (COUNT (?gender) as ?count) ?genderLabel
WHERE {
  ?episode (wdt:P31/(wdt:P279*)) wd:Q1983062;
    p:P179 ?statement;
    wdt:P5030 ?guest.
  ?statement ps:P179 wd:Q56816469;
    pq:P1545 ?numero_episode.
  ?guest wdt:P21 ?gender.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?gender rdfs:label ?genderLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
GROUP BY ?genderLabel

Try it!

# Profession des invités dans Interdit d'interdire
SELECT DISTINCT (COUNT (?occupation) as ?count) ?occupationLabel
WHERE {
  ?episode (wdt:P31/(wdt:P279*)) wd:Q1983062;
    p:P179 ?statement;
    wdt:P5030 ?guest.
  ?statement ps:P179 wd:Q56816469;
    pq:P1545 ?numero_episode.
  ?guest wdt:P21 ?gender.
  ?guest wdt:P106 ?occupation.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?gender rdfs:label ?genderLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
GROUP BY ?occupationLabel
ORDER BY DESC (?count)

Try it!

# https://w.wiki/6h$$ 
# https://w.wiki/6i23

# Parité homme/femme dans les émissions TV de Frédéric Taddéï
SELECT $series $seriesLabel ((?totalMale*100)/?totalGuests AS ?malePercent) ((?totalFemale*100)/?totalGuests AS ?femalePercent) 
WHERE {
  {
    SELECT $series (SUM(?male) AS ?totalMale) (SUM(?female) AS ?totalFemale) ((SUM(?female) + SUM(?male)) AS ?totalGuests) 
    WHERE {
      $item wdt:P31 wd:Q21191270 .
      $item wdt:P179 $series ; wdt:P5030 ?guest .
      $series wdt:P371 wd:Q603 .
      ?guest wdt:P21 $gender .
  
      BIND (
        IF(?gender = wd:Q6581097 , 1, 0) AS ?male
      )
      BIND (
        IF(?gender = wd:Q6581072 , 1, 0) AS ?female
      )
    }
    GROUP BY $series $seriesLabel
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}

# https://w.wiki/6i2Q
# https://w.wiki/6i5Y

Try it!

Doubloug Prize

#Swedish recipients
SELECT DISTINCT ?year_received (GROUP_CONCAT(DISTINCT ?itemLabel; SEPARATOR = ", ") AS ?swedish_recipients) 
WHERE {
  ?item wdt:P27 wd:Q34;
    p:P166 ?prize.
  ?prize ps:P166 wd:Q1233326;
    pq:P585 ?date_received.
  BIND(YEAR(?date_received) AS ?year_received)
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "fr,en,de,es". 
    ?item rdfs:label ?itemLabel.
  }
}
GROUP BY ?year_received
ORDER BY (?year_received)

Try it!

#Join Swedish and Norway recipients
SELECT ?year_received ?swedish_recipients ?norway_recipients
WHERE {
{
  SELECT DISTINCT ?year_received (GROUP_CONCAT(DISTINCT ?itemLabel; SEPARATOR = ", ") AS ?swedish_recipients) 
  WHERE {
    ?item wdt:P27 wd:Q34; p:P166 ?prize.
    ?prize ps:P166 wd:Q1233326; pq:P585 ?date_received.
    BIND(YEAR(?date_received) AS ?year_received)
    SERVICE wikibase:label { 
      bd:serviceParam wikibase:language "fr,en,de,es". 
      ?item rdfs:label ?itemLabel.
    }
  }
  GROUP BY ?year_received
}
{
  SELECT DISTINCT ?year_received (GROUP_CONCAT(DISTINCT ?itemLabel; SEPARATOR = ", ") AS ?norway_recipients) 
  WHERE {
    ?item wdt:P27 wd:Q20; p:P166 ?prize.
    ?prize ps:P166 wd:Q1233326; pq:P585 ?date_received.
    BIND(YEAR(?date_received) AS ?year_received)
    SERVICE wikibase:label { 
      bd:serviceParam wikibase:language "fr,en,de,es". 
      ?item rdfs:label ?itemLabel.
    }
  }
  GROUP BY ?year_received
}
}
ORDER BY (?year_received)

Try it!

Streets stuff

# Streets in Nantes that does or not refer to something. 
SELECT DISTINCT ?item ?itemLabel ?namedAfterLabel WHERE { 
  ?item (wdt:P31/(wdt:P279*)) wd:Q83620 ; wdt:P131 wd:Q12191 . 
  OPTIONAL { ?item wdt:P138 ?namedAfter }
  # FILTER (!BOUND(?namedAfter)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en,ru". }

}
ORDER BY (?itemLabel)

Try it!

# count of streets of France by cities (WIP) 
SELECT DISTINCT (COUNT(?city) AS ?count) ?city ?cityLabel WHERE {
  ?item wdt:P31 wd:Q79007; wdt:P17 wd:Q142.
  ?item wdt:P131 ?city.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?city rdfs:label ?cityLabel.
  }
}
GROUP BY ?city ?cityLabel
ORDER BY DESC(xsd:integer(?count))

Try it!

# streets of Villeurbanne (WIP) 
SELECT DISTINCT ?item ?itemLabel ?namedAfterLabel WHERE {
  ?item (wdt:P31/(wdt:P279*)) wd:Q83620 ; wdt:P131 wd:Q582 .
  OPTIONAL { ?item wdt:P138 ?namedAfter. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en,ru". }
}
ORDER BY (?itemLabel)

Try it!

NBER

# duplicates
SELECT * WHERE {
{
  SELECT $doi $itemNber
  WHERE {
    $itemNber wdt:P179 wd:Q57081850 ; wdt:P356 $doi.
  }
}
{
  SELECT $doi $itemNonNber
  WHERE {
    $itemNonNber wdt:P31 wd:Q13442814 ; wdt:P356 $doi.
    FILTER (BOUND(?doi)) .
    OPTIONAL { $itemNonNber wdt:P179 ?series } .
    FILTER (!BOUND(?series)) .           
  }
}
}

Try it!

#List of authors per number of articles descending ordered
SELECT DISTINCT (COUNT(?author) AS ?count) ?author WHERE {
  ?article wdt:P31 wd:Q13442814; wdt:P179 wd:Q57081850.
  OPTIONAL { ?article wdt:P2093 ?author. }
}
GROUP BY ?author
ORDER BY DESC (?count)

Try it!

# random stuff I need to persist
SELECT DISTINCT ?item ?itemLabel WHERE {
  ?item (p:P31/ps:P31/(wdt:P279*)) wd:Q13442814 .
  ?item (p:P2093/ps:P2093) "Françoise Masnou-Seeuws".
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
}

Try it!

# select articles with one single author (author as a string), 
# and select the author (which can't be done in the same query, it has to be made in a join)
SELECT $item $count ?author WHERE {
  FILTER (?count = 1)
  {
    SELECT DISTINCT ?item (COUNT (?author) as ?count) WHERE {
      ?item wdt:P31 wd:Q13442814 ; wdt:P179 wd:Q57081850 .
      ?item wdt:P2093 ?author.
    }
    GROUP BY (?item)
  }
  {
    SELECT DISTINCT ?item ?author WHERE {
      ?item wdt:P31 wd:Q13442814 ; wdt:P179 wd:Q57081850 .
      ?item wdt:P2093 ?author.
    }
  }
}

Try it!

# better version of the one above: we select only articles that have one single author
# (excluding the case where there are multiple authors but just one is a "string" author)
SELECT $item $count ?author_name ?rank  WHERE {
  # FILTER REGEX(STR(?count), "^[1]{1}$")
  # FILTER REGEX(STR(?rank), "^[1]{1}$")
  # FILTER (?count >= 7)
  FILTER (?count = 1)
  FILTER (STR(?rank) = "1")
  {
    SELECT DISTINCT ?item (COUNT (?author) as ?count) WHERE {
      ?item wdt:P31 wd:Q13442814 ; wdt:P179 wd:Q57081850 .
      ?item wdt:P2093 ?author.
    }
    GROUP BY (?item)
  }
  {
    SELECT DISTINCT ?item (xsd:string(?author) AS ?author_name) ?rank WHERE {
      ?item wdt:P31 wd:Q13442814 ; wdt:P179 wd:Q57081850 .
      ?item p:P2093 [ ps:P2093 ?author ; pq:P1545 ?rank ] .
    }
  }
}

Try it!

# better version of the one above
SELECT $item ?instance_author_count $string_author_count ?author_name ?rank WHERE {
  # FILTER (?count >= 7)
  FILTER (?string_author_count = 1)
  FILTER (?instance_author_count = 1)
  FILTER (STR(?rank) = "2")
  {
    SELECT DISTINCT ?item (COUNT (?author) as ?string_author_count) WHERE {
      ?item wdt:P31 wd:Q13442814 ; 
            wdt:P179 wd:Q57081850 .
      ?item wdt:P2093 ?author.
    }
    GROUP BY (?item)
  }
  {
    SELECT DISTINCT ?item (COUNT (?instance_author) as ?instance_author_count) WHERE {
      ?item wdt:P31 wd:Q13442814 ; 
            wdt:P179 wd:Q57081850 .
      OPTIONAL { ?item wdt:P50 ?instance_author. }
    }
    GROUP BY (?item)
  }
  {
    SELECT DISTINCT ?item (xsd:string(?author) AS ?author_name) ?rank WHERE {
      ?item wdt:P31 wd:Q13442814 ; 
            wdt:P179 wd:Q57081850 .
      ?item p:P2093 [ 
        ps:P2093 ?author ; 
        pq:P1545 ?rank 
      ] .
    }
  }
}

Try it!

# select any economist that has a label like the string
SELECT $item ?label WHERE {
  ?item wdt:P31 wd:Q5;
    wdt:P106 wd:Q188094;
    rdfs:label ?label .
  FILTER(LANG(?label) IN ("en")) .
  FILTER(CONTAINS(?label, "Victor Zarnowitz")) .
}

Try it!

# select nber articles with one single author (author as a string), 
# select economists
# join economist name with single author string.
# without the limit, we're in timeout.

SELECT $item $count ?author_name $a WHERE {
  # FILTER REGEX(STR(?count), "^[1]{1}$")
  FILTER (?count = 1)
  {
    SELECT DISTINCT ?item (COUNT (?author) as ?count) WHERE {
      ?item wdt:P31 wd:Q13442814 ; wdt:P179 wd:Q57081850 .
      ?item wdt:P2093 ?author.
    }
    GROUP BY (?item)
  }
  {
    SELECT DISTINCT ?item (xsd:string(?author) AS ?author_name) WHERE {
      ?item wdt:P31 wd:Q13442814 ; wdt:P179 wd:Q57081850 .
      ?item wdt:P2093 ?author.
    }
  }
  {
    SELECT (xsd:string(?author) AS ?author_name) $a WHERE {
      ?a wdt:P31 wd:Q5;
         wdt:P106 wd:Q188094;
         rdfs:label ?author .
      FILTER(LANG(?author) IN ("en")) .
    }
    LIMIT 3000
  }
}

Try it!

wp:ht

# count of haitian articles with no interwiki links and with no statements
SELECT DISTINCT (COUNT(?item) AS ?count) WHERE {
    ?item wikibase:statements ?statement_count .
  	?item wikibase:sitelinks ?sitelink_count .
    ?sitelink schema:about ?item ; 
              schema:inLanguage "ht" .
    FILTER (?sitelink_count = 1)
    FILTER (?statement_count = 0)
}

Try it!

Press

# simple, article published in Liberation
SELECT $q $label
WHERE {
  $q wdt:P1433 wd:Q13717; rdfs:label ?label .
}

Try it!

SELECT ?q ?qLabel $genderLabel $url $goog WHERE { 
  $q wdt:P31 wd:Q5 ;
     p:P6872 $n . 
  OPTIONAL {
   $q wdt:P21 $gender . 
  }
  OPTIONAL {
   $q wdt:P2671 $google . 
  }
  OPTIONAL {
   $q wdt:P646 $freebase . 
  }
  $n ps:P6872 wd:Q164746 .
  OPTIONAL { 
    $n pq:P2699 $url .
  }
  BIND(COALESCE(?google, ?freebase) as ?goog) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}

Try it!

# journalists with aliases for downstream processing
SELECT DISTINCT ?item ?itemLabel ?genderLabel (URI(?url) AS ?lien) ?itemAltLabel WHERE {
  ?item wdt:P31 wd:Q5;
    p:P6872 ?n.
  ?n ps:P6872 wd:Q164746.
  OPTIONAL { ?item wdt:P21 ?gender. }
  OPTIONAL { ?n pq:P2699 ?url. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
}
ORDER BY ?itemLabel

Try it!

# WSJ articles
SELECT DISTINCT ?item ?itemLabel ?date ?articleId ?wordCount ?url WHERE {
  ?item wdt:P1433 wd:Q164746.
  hint:Prior hint:runFirst "true"^^xsd:boolean.
  ?item (wdt:P31/(wdt:P279*)) wd:Q191067;
    p:P1433 ?publishedIn.
  OPTIONAL { ?publishedIn pq:P2322 ?articleId. }
  OPTIONAL { ?item wdt:P953 ?url. }
  OPTIONAL { ?item wdt:P577 ?date. }
  OPTIONAL { ?item wdt:P6570 ?wordCount. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}

Try it!

# WSJ journalists ordered by number of articles in Wikidata (including the ones w 0 articles)
SELECT ?author ?authorLabel (COALESCE((SAMPLE(?count)), 0) AS $totalArticles) WHERE {
  {
    SELECT DISTINCT ?author ?count WHERE {
      ?author wdt:P31 wd:Q5;
        p:P6872 ?n.
      ?n ps:P6872 wd:Q164746.
    }
  } UNION {
    SELECT DISTINCT ?author (COUNT(?item) AS ?count) 
    WHERE {  
      ?item wdt:P1433 wd:Q164746.
      hint:Prior hint:runFirst "true"^^xsd:boolean.
      ?item (wdt:P31/(wdt:P279*)) wd:Q191067;
        wdt:P50 ?author.
    }
    GROUP BY ?author
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?author ?authorLabel
ORDER BY DESC ($totalArticles)

Try it!

# WSJ articles contains a subject in title that is not set as a subject
SELECT DISTINCT ?item ?itemLabel WHERE {
  ?item wdt:P1433 wd:Q164746.
  hint:Prior hint:runFirst "true"^^xsd:boolean.
  ?item (wdt:P31/(wdt:P279*)) wd:Q191067;
    p:P1433 ?publishedIn;
    rdfs:label ?label .
  
  OPTIONAL { ?publishedIn pq:P2322 ?articleId. }
  OPTIONAL { ?item wdt:P953 ?url. }
  OPTIONAL { ?item wdt:P577 ?date. }
  OPTIONAL { ?item wdt:P921 ?currentSubjects. }
  
  FILTER(CONTAINS(?label, "Iran")) .
  BIND( wd:Q794 as ?subject )
  FILTER NOT EXISTS {
    ?item wdt:P921 $subject .
  } .  
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}
GROUP BY ?item ?itemLabel

Try it!

# maintenance query: WSJ articles without unique article ID (should be empty)
SELECT DISTINCT ?item ?itemLabel ?date ?articleId ?url WHERE {  
  ?item wdt:P1433 wd:Q164746.
  hint:Prior hint:runFirst "true"^^xsd:boolean.
  ?item (wdt:P31/(wdt:P279*)) wd:Q191067;
    p:P1433 ?publishedIn.
  OPTIONAL { ?publishedIn pq:P2322 ?articleId. }
  OPTIONAL { ?item wdt:P953 ?url. }
  OPTIONAL { ?item wdt:P577 ?date. }
  
  FILTER(?date >= "1996-03-01T00:00:00"^^xsd:dateTime)
  FILTER(!BOUND(?articleId))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}

Try it!

# journalist strings by number of articles
# So we can know who to create
# Improvement: join with existing journalist label! Boom!
SELECT DISTINCT (COUNT(?item) AS ?count) ?authorString
WHERE {  
  ?item wdt:P1433 wd:Q164746.
  hint:Prior hint:runFirst "true"^^xsd:boolean.
  ?item (wdt:P31/(wdt:P279*)) wd:Q191067;
    wdt:P2093 ?authorString.
}
GROUP BY ?authorString
ORDER BY DESC (?count)

Try it!

# WSJ articles' main subjects order by count
# and the winner is... OPEC!
SELECT DISTINCT (COUNT(?item) AS ?count) ?subject $subjectLabel
WHERE {  
  ?item wdt:P1433 wd:Q164746.
  hint:Prior hint:runFirst "true"^^xsd:boolean.
  ?item (wdt:P31/(wdt:P279*)) wd:Q191067;
    wdt:P921 ?subject.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
}
GROUP BY ?subject $subjectLabel
ORDER BY DESC (?count)

Try it!

# daily newspapers by number of articles in WD
SELECT (COUNT($q) AS $count) $publisher $publisherLabel
WHERE {
  $q wdt:P1433 $publisher .
  $publisher wdt:P31 wd:Q1110794 .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
}
GROUP BY ?publisher $publisherLabel
ORDER BY DESC ($count)

Try it!

SELECT $author ?articleId WHERE {
{
  SELECT $author WHERE {
    $author wdt:P31 wd:Q5 ; 
            wdt:P6872 wd:Q164746 ; 
            p:P6872 $n .  
      $n pq:P2699 $url .
    # FILTER(STR(?url) = "https://www.wsj.com/news/author/margot-patrick" )
    FILTER(STRENDS(STR(?url), "margot-patrick")) .
  }
}
{
  SELECT DISTINCT $author ?articleId  WHERE {
    ?item wdt:P1433 wd:Q164746.
    hint:Prior hint:runFirst "true"^^xsd:boolean.
    ?item (wdt:P31/(wdt:P279*)) wd:Q191067;
      p:P1433 ?publishedIn.
    $item wdt:P50 $author.

  ?publishedIn pq:P2322 ?articleId.    
  }
  GROUP BY $author ?articleId
}

}

Try it!

Libé

# list of portraits with author and subject
SELECT ?item ?itemLabel ?author ?authorLabel ?subject ?subjectLabel
WHERE {  
  ?item wdt:P361 wd:Q30091381 .  
  hint:Prior hint:runFirst "true"^^xsd:boolean.
  ?item wdt:P50 ?author . 
  OPTIONAL { ?item wdt:P921 ?subject . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}

Try it!

NYT Obituaries

Gold: Wikidata:Database reports/Deaths at Wikipedia/2022

# good query to check errors. 
# provides the difference (in days) between obituary publication date and death date (can be filtered against a given value).
SELECT ?q ?qLabel ?death_date ?obi_date ?diff ?obituary ?obituaryLabel  WHERE {
  ?q wdt:P31 wd:Q5 ; wdt:P1343 ?obituary .
  ?obituary wdt:P31 wd:Q309481 ; wdt:P1433 wd:Q9684 .
  ?obituary wdt:P577 ?obi_date .
  OPTIONAL { ?q wdt:P570 ?death_date } .
  
  FILTER(BOUND(?death_date)) . # FILTER(!BOUND(?death_date)) will filter only the ones who have no date of death 
  
  ?obituary rdfs:label ?label .  
  FILTER(!STRSTARTS(?label, "Overlooked") ) . # this is because there's an obituary's series called "Overlooked No More" about past personalities.
  
  BIND((?obi_date - ?death_date) as ?diff).
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
}
GROUP BY ?q ?qLabel ?death_date ?obi_date ?diff ?obituary ?obituaryLabel
ORDER BY DESC(?diff)

Try it!

# an other query to check errors. with occupation to check if same person. 
SELECT ?q ?qLabel ?occLabel  ?publicationDate ?mainSubject  WHERE {
  ?q wdt:P31 wd:Q309481 ; wdt:P1433 wd:Q9684 .
  ?q wdt:P577 ?publicationDate .
  ?q wdt:P921 ?mainSubject . 
  ?mainSubject wdt:P106 ?occ . 
  # OPTIONAL { ?mainSubject wdt:P570 ?mainSubjectDeathDate . }
  
  FILTER(YEAR(?publicationDate) = 2015).
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,nl". }
}

Try it!

# obituaries without main subject
# filtered by year
SELECT ?q ?qLabel ?publicationDate ?mainSubject ?mainSubjectLabel WHERE {
  ?q wdt:P31 wd:Q309481 ; wdt:P1433 wd:Q9684 .
  ?q wdt:P577 ?publicationDate .
  OPTIONAL { ?q wdt:P921 ?mainSubject . }
  # OPTIONAL { ?mainSubject wdt:P570 ?mainSubjectDeathDate . }
  
  # FILTER(?publicationDate >= "2018-01-01"^^xsd:dateTime && ?publicationDate < "2019-01-01"^^xsd:dateTime) .
  FILTER(YEAR(?publicationDate) = 2018).
  FILTER(!BOUND(?mainSubject)).
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,nl". }
}
ORDER BY DESC(?mainSubject)

Try it!

# number of NYT obituaries per year
SELECT ?year (COUNT(?q) AS ?count) WHERE {
  SELECT DISTINCT ?q (YEAR(?date) AS ?year) (MONTH(?date) AS ?month) WHERE {
    ?q wdt:P31 wd:Q309481 ; wdt:P1433 wd:Q9684 .
    ?q wdt:P577 ?date.
    FILTER(?date >= "2006-01-01"^^xsd:dateTime && ?date < "2024-01-01"^^xsd:dateTime) .
  }
}
GROUP BY ?year
ORDER BY ?year

Try it!

# wrong
SELECT ?obituaries ?autho ?authoLabel ?year WHERE {
  {
	SELECT (MAX(?total_obituaries) as ?obituaries) (MAX(?author) as ?autho) ?year WHERE {
	  {
	    SELECT ?author (count(?q) as ?total_obituaries) ?year WHERE {
	      ?q wdt:P1433 wd:Q9684 ; wdt:P31 wd:Q309481 .
	      ?q wdt:P50 ?author .
	      ?q wdt:P577 ?date .
	      BIND(YEAR(?date) AS ?year) .
	      FILTER(?year IN (2022, 2021)).
	    }
	    GROUP BY ?author ?year
	  }
	}
	GROUP BY ?year  
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
}

Try it!

Real Time with Bill Maher

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

#List of episodes with aggregated comma-separated guests
SELECT DISTINCT ?episode ?numero_episode ?date ?episodeLabel (GROUP_CONCAT(DISTINCT ?guestLabel; SEPARATOR = ", ") AS ?liste_invites)
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?episode wdt:P577 ?date.
  ?statement ps:P179 wd:Q2030903.
  ?statement pq:P1545 ?numero_episode.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en,fr".
    ?guest rdfs:label ?guestLabel.
    ?episode rdfs:label ?episodeLabel.
  }
}
GROUP BY ?episode ?numero_episode ?date ?episodeLabel
ORDER BY DESC(xsd:integer(?numero_episode))

Try it!

#List of guests per number of appearance
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT (COUNT (?guest) as ?count) ?guestLabel 
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?statement ps:P179 wd:Q2030903.
  ?statement pq:P1545 ?numero_episode.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en".
                         ?guest rdfs:label ?guestLabel.}
}
GROUP BY (?guestLabel)
ORDER BY DESC (?count)

Try it!

#Parité homme/femme dans Real Time

SELECT DISTINCT (COUNT (?gender) as ?count) ?genderLabel
WHERE {
  ?episode (wdt:P31/(wdt:P279*)) wd:Q1983062;
    p:P179 ?statement;
    wdt:P5030 ?guest.
  ?statement ps:P179 wd:Q2030903;
    pq:P1545 ?numero_episode.
  ?guest wdt:P21 ?gender.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?gender rdfs:label ?genderLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
GROUP BY ?genderLabel

Try it!

Misc

SELECT DISTINCT ?item ?itemLabel ?itemDescription WHERE{  
  VALUES ?libelles { 
    "Klaus Nomi"@en
    "Ray Edenton"@en
  } 
  ?item ?label ?libelles.
  ?item wdt:P31 wd:Q5.
  ?article schema:about ?item .
  ?article schema:inLanguage "en" .
  ?article schema:isPartOf <https://en.wikipedia.org/>. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }    
}

Try it!

# items that have old akadem but not new akadem
SELECT ?q ?qLabel ?akadem_old ?akadem_new WHERE {
  ?q wdt:P5378 ?akadem_old .
  OPTIONAL { ?q wdt:P12214 ?akadem_new } .
  FILTER(!BOUND(?akadem_new)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}

Try it!

User:Deansfa

Contents

MyQ

Projects

Tracked items

Some SPARQL stuff of my own

Articles that are "Featured", "Good" or "Features list" in one language and don't exist in an other

Horse breeds that don't have an article in French

People born in 1899 in Polotsk

Poems by Arthur Rimbaud

Poems that has Ophélie in their label

Velodromes in the United States

Architecture

Interdit d'interdire

Doubloug Prize

Streets stuff

NBER

wp:ht

Press

Libé

NYT Obituaries

Real Time with Bill Maher

Misc

Navigation menu

User:Deansfa

MyQ

Projects

Tracked items

Some SPARQL stuff of my own

Articles that are "Featured", "Good" or "Features list" in one language and don't exist in an other

Horse breeds that don't have an article in French

People born in 1899 in Polotsk

Poems by Arthur Rimbaud

Poems that has Ophélie in their label

Velodromes in the United States

Architecture

Interdit d'interdire

Doubloug Prize

Streets stuff

NBER

wp:ht

Press

Libé

NYT Obituaries

Real Time with Bill Maher

Misc

Navigation menu

Search