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!