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.
Articles that are "Featured", "Good" or "Features list" in one language and don't exist in an other[edit]
# Featured/Good articles in Korean that don't have a French Wikipedia pageSELECTDISTINCT?item?itemLabelWHERE{?featuredArticleschema:about?item;schema:inLanguage"ko";wikibase:badge?badgeValues.VALUES?badgeValues{wd:Q17437796# that are featured articleswd:Q17506997# or featured listswd:Q17437798# or good articles}OPTIONAL{?sitelinkschema:about?item;schema:inLanguage"fr".}FILTER(!BOUND(?sitelink))SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en,ja,ko".}}ORDER BY?itemLabel
# People born in 1899 in PolotskSELECTDISTINCT?item?itemLabelWHERE{?itemwdt:P19wd:Q200797;wdt:P569?date.FILTER(YEAR(?date)=1899)SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en,ru".}}ORDER BY?itemLabel
#Locations of velodromes#defaultView:MapSELECT?objectLabel?objectDescription?link?coordWHERE{?objectwdt:P31wd:Q830528;wdt:P625?coord.SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en"}}
#Buildings on Park Avenue#defaultView:MapSELECT?objectLabel?objectDescription?link?coordWHERE{?objectwdt:P669wd:Q109711;wdt:P625?coord.SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en"}}
#Buildings with Park Avenue in their label that don't have the property "voie" set SELECTDISTINCT?objectLabel?objectDescription?voieLabelWHERE{?objectwdt:P31wd:Q13402009;rdfs:label?label.OPTIONAL{?objectwdt:P669?voie}FILTER(CONTAINS(?label,"Park Avenue")).FILTER(!BOUND(?voie)).SERVICEwikibase:label{bd:serviceParamwikibase:language"fr"}}
#a little of a repeat of the previous one.#apartment buildings in the USA that don't have the property "voie" set SELECTDISTINCT?objectLabel?objectDescription?voieLabelWHERE{?objectwdt:P17wd:Q30;wdt:P31wd:Q13402009;rdfs:label?label.OPTIONAL{?objectwdt:P669?voie}#FILTER(CONTAINS(?label, "89th Street")) .FILTER(!BOUND(?voie)).SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en"}}
#Buildings on Park Avenue that don't have a street number SELECTDISTINCT?object?objectLabel?voieLabel?streetNumberWHERE{?objectwdt:P669wd:Q109711.?objectp:P669?voie.OPTIONAL{?voiepq:P670?streetNumber}.FILTER(!BOUND(?streetNumber)).SERVICEwikibase:label{bd:serviceParamwikibase:language"fr"}}
#Apartment buildings in Manhattan that don't have coordonates SELECT?object?objectLabel$objectDescription$coordWHERE{?objectwdt:P31wd:Q13402009.?objectwdt:P131wd:Q11299.OPTIONAL{?objectwdt:P625?coord}.FILTER(!BOUND(?coord)).SERVICEwikibase:label{bd:serviceParamwikibase:language"fr".}}
# REGEX usage example:# buildings in Manhattan that have "Drive", "Avenue" or "Street" in their labelSELECTDISTINCT?object?objectLabel?voieLabel?streetNumberWHERE{?objectwdt:P131wd:Q11299;rdfs:label?label.?objectp:P669?voie.OPTIONAL{?voiepq:P670?streetNumber.}SERVICEwikibase:label{bd:serviceParamwikibase:language"en,fr".}FILTERREGEX(STR(?label),"Drive|Avenue|Street")FILTER(!BOUND(?streetNumber))}
# churches taller than 100 meters in FranceSELECT$item$itemLabel?elevation?picWHERE{$item(wdt:P31/(wdt:P279*))wd:Q16970;wdt:P17wd:Q142.?itemp:P2048?st.?stpsn:P2048$height.?heightwikibase:quantityAmount?elevation.FILTER(?elevation>100).?heightwikibase:quantityUnitwd:Q11573.OPTIONAL{?itemwdt:P18?pic.}SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".}}GROUP BY$item$itemLabel?elevation?pic
PREFIXxsd:<http://www.w3.org/2001/XMLSchema#>#List of episodes with aggregated comma-separated guestsSELECTDISTINCT?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.?episodep:P179?statement.OPTIONAL{?episodewdt:P5030?guest.}?episodewdt:P577?date.?statementps:P179wd:Q56816469.?statementpq:P1545?numero_episode.OPTIONAL{?episodewdt:P1651?youtube.}SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".?guestrdfs:label?guestLabel.?episoderdfs:label?episodeLabel.}}GROUP BY?episode?numero_episode?date?episodeLabel?youtubeORDER BYDESC(xsd:integer(?numero_episode))
PREFIXxsd:<http://www.w3.org/2001/XMLSchema#>#List of episodes with episode number per season and aggregated comma-separated guestsSELECTDISTINCT?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.?episodep:P179?statement.?episodep:P4908?season.OPTIONAL{?episodewdt:P5030?guest.}?episodewdt:P577?date.?statementps:P179wd:Q56816469.?statementpq:P1545?no_episode.?seasonpq:P1545?no_episode_saison.OPTIONAL{?episodewdt:P1651?youtube.}SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".?guestrdfs:label?guestLabel.?episoderdfs:label?episodeLabel.}}GROUP BY?episode?no_episode?no_episode_saison?date?episodeLabel?youtubeORDER BYDESC(xsd:integer(?no_episode))
#List of guests per number of appearancePREFIXxsd:<http://www.w3.org/2001/XMLSchema#>SELECTDISTINCT(COUNT(?guest)as?count)?guestLabelWHERE{?episode(wdt:P31/wdt:P279*)wd:Q1983062.?episodep:P179?statement.OPTIONAL{?episodewdt:P5030?guest.}?statementps:P179wd:Q56816469.?statementpq:P1545?numero_episode.SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".?guestrdfs:label?guestLabel.}}GROUP BY(?guestLabel)ORDER BYDESC(?count)
# Profession des invités dans Interdit d'interdireSELECTDISTINCT(COUNT(?occupation)as?count)?occupationLabelWHERE{?episode(wdt:P31/(wdt:P279*))wd:Q1983062;p:P179?statement;wdt:P5030?guest.?statementps:P179wd:Q56816469;pq:P1545?numero_episode.?guestwdt:P21?gender.?guestwdt:P106?occupation.SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".?guestrdfs:label?guestLabel.?genderrdfs:label?genderLabel.?occupationrdfs:label?occupationLabel.}}GROUP BY?occupationLabelORDER BYDESC(?count)
# 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)/?totalGuestsAS?malePercent)((?totalFemale*100)/?totalGuestsAS?femalePercent)WHERE{{SELECT$series(SUM(?male)AS?totalMale)(SUM(?female)AS?totalFemale)((SUM(?female)+SUM(?male))AS?totalGuests)WHERE{$itemwdt:P31wd:Q21191270.$itemwdt:P179$series;wdt:P5030?guest.$serieswdt:P371wd:Q603.?guestwdt:P21$gender.BIND(IF(?gender=wd:Q6581097,1,0)AS?male)BIND(IF(?gender=wd:Q6581072,1,0)AS?female)}GROUP BY$series$seriesLabel}SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".}}# https://w.wiki/6i2Q# https://w.wiki/6i5Y
#Join Swedish and Norway recipientsSELECT?year_received?swedish_recipients?norway_recipientsWHERE{{SELECTDISTINCT?year_received(GROUP_CONCAT(DISTINCT?itemLabel;SEPARATOR=", ")AS?swedish_recipients)WHERE{?itemwdt:P27wd:Q34;p:P166?prize.?prizeps:P166wd:Q1233326;pq:P585?date_received.BIND(YEAR(?date_received)AS?year_received)SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en,de,es".?itemrdfs:label?itemLabel.}}GROUP BY?year_received}{SELECTDISTINCT?year_received(GROUP_CONCAT(DISTINCT?itemLabel;SEPARATOR=", ")AS?norway_recipients)WHERE{?itemwdt:P27wd:Q20;p:P166?prize.?prizeps:P166wd:Q1233326;pq:P585?date_received.BIND(YEAR(?date_received)AS?year_received)SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en,de,es".?itemrdfs:label?itemLabel.}}GROUP BY?year_received}}ORDER BY(?year_received)
# Streets in Nantes that does or not refer to something. SELECTDISTINCT?item?itemLabel?namedAfterLabelWHERE{?item(wdt:P31/(wdt:P279*))wd:Q83620;wdt:P131wd:Q12191.OPTIONAL{?itemwdt:P138?namedAfter}# FILTER (!BOUND(?namedAfter)) .SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en,ru".}}ORDER BY(?itemLabel)
# count of streets of France by cities (WIP) SELECTDISTINCT(COUNT(?city)AS?count)?city?cityLabelWHERE{?itemwdt:P31wd:Q79007;wdt:P17wd:Q142.?itemwdt:P131?city.SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".?cityrdfs:label?cityLabel.}}GROUP BY?city?cityLabelORDER BYDESC(xsd:integer(?count))
#List of authors per number of articles descending orderedSELECTDISTINCT(COUNT(?author)AS?count)?authorWHERE{?articlewdt:P31wd:Q13442814;wdt:P179wd:Q57081850.OPTIONAL{?articlewdt:P2093?author.}}GROUP BY?authorORDER BYDESC(?count)
# random stuff I need to persistSELECTDISTINCT?item?itemLabelWHERE{?item(p:P31/ps:P31/(wdt:P279*))wd:Q13442814.?item(p:P2093/ps:P2093)"Françoise Masnou-Seeuws".SERVICEwikibase:label{bd:serviceParamwikibase:language"en,fr".}}
# 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?authorWHERE{FILTER(?count=1){SELECTDISTINCT?item(COUNT(?author)as?count)WHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemwdt:P2093?author.}GROUP BY(?item)}{SELECTDISTINCT?item?authorWHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemwdt:P2093?author.}}}
# 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?rankWHERE{# FILTER REGEX(STR(?count), "^[1]{1}$")# FILTER REGEX(STR(?rank), "^[1]{1}$")# FILTER (?count >= 7)FILTER(?count=1)FILTER(STR(?rank)="1"){SELECTDISTINCT?item(COUNT(?author)as?count)WHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemwdt:P2093?author.}GROUP BY(?item)}{SELECTDISTINCT?item(xsd:string(?author)AS?author_name)?rankWHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemp:P2093[ps:P2093?author;pq:P1545?rank].}}}
# better version of the one aboveSELECT$item?instance_author_count$string_author_count?author_name?rankWHERE{# FILTER (?count >= 7)FILTER(?string_author_count=1)FILTER(?instance_author_count=1)FILTER(STR(?rank)="2"){SELECTDISTINCT?item(COUNT(?author)as?string_author_count)WHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemwdt:P2093?author.}GROUP BY(?item)}{SELECTDISTINCT?item(COUNT(?instance_author)as?instance_author_count)WHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.OPTIONAL{?itemwdt:P50?instance_author.}}GROUP BY(?item)}{SELECTDISTINCT?item(xsd:string(?author)AS?author_name)?rankWHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemp:P2093[ps:P2093?author;pq:P1545?rank].}}}
# select any economist that has a label like the stringSELECT$item?labelWHERE{?itemwdt:P31wd:Q5;wdt:P106wd:Q188094;rdfs:label?label.FILTER(LANG(?label)IN("en")).FILTER(CONTAINS(?label,"Victor Zarnowitz")).}
# 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$aWHERE{# FILTER REGEX(STR(?count), "^[1]{1}$")FILTER(?count=1){SELECTDISTINCT?item(COUNT(?author)as?count)WHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemwdt:P2093?author.}GROUP BY(?item)}{SELECTDISTINCT?item(xsd:string(?author)AS?author_name)WHERE{?itemwdt:P31wd:Q13442814;wdt:P179wd:Q57081850.?itemwdt:P2093?author.}}{SELECT(xsd:string(?author)AS?author_name)$aWHERE{?awdt:P31wd:Q5;wdt:P106wd:Q188094;rdfs:label?author.FILTER(LANG(?author)IN("en")).}LIMIT3000}}
# count of haitian articles with no interwiki links and with no statementsSELECTDISTINCT(COUNT(?item)AS?count)WHERE{?itemwikibase:statements?statement_count.?itemwikibase:sitelinks?sitelink_count.?sitelinkschema:about?item;schema:inLanguage"ht".FILTER(?sitelink_count=1)FILTER(?statement_count=0)}
# journalists with aliases for downstream processingSELECTDISTINCT?item?itemLabel?genderLabel(URI(?url)AS?lien)?itemAltLabelWHERE{?itemwdt:P31wd:Q5;p:P6872?n.?nps:P6872wd:Q164746.OPTIONAL{?itemwdt:P21?gender.}OPTIONAL{?npq:P2699?url.}SERVICEwikibase:label{bd:serviceParamwikibase:language"en,fr".}}ORDER BY?itemLabel
# 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{{SELECTDISTINCT?author?countWHERE{?authorwdt:P31wd:Q5;p:P6872?n.?nps:P6872wd:Q164746.}}UNION{SELECTDISTINCT?author(COUNT(?item)AS?count)WHERE{?itemwdt:P1433wd:Q164746.hint:Priorhint:runFirst"true"^^xsd:boolean.?item(wdt:P31/(wdt:P279*))wd:Q191067;wdt:P50?author.}GROUP BY?author}SERVICEwikibase:label{bd:serviceParamwikibase:language"en".}}GROUP BY?author?authorLabelORDER BYDESC($totalArticles)
# WSJ articles contains a subject in title that is not set as a subjectSELECTDISTINCT?item?itemLabelWHERE{?itemwdt:P1433wd:Q164746.hint:Priorhint:runFirst"true"^^xsd:boolean.?item(wdt:P31/(wdt:P279*))wd:Q191067;p:P1433?publishedIn;rdfs:label?label.OPTIONAL{?publishedInpq:P2322?articleId.}OPTIONAL{?itemwdt:P953?url.}OPTIONAL{?itemwdt:P577?date.}OPTIONAL{?itemwdt:P921?currentSubjects.}FILTER(CONTAINS(?label,"Iran")).BIND(wd:Q794as?subject)FILTERNOT EXISTS{?itemwdt:P921$subject.}.SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".}}GROUP BY?item?itemLabel
# maintenance query: WSJ articles without unique article ID (should be empty)SELECTDISTINCT?item?itemLabel?date?articleId?urlWHERE{?itemwdt:P1433wd:Q164746.hint:Priorhint:runFirst"true"^^xsd:boolean.?item(wdt:P31/(wdt:P279*))wd:Q191067;p:P1433?publishedIn.OPTIONAL{?publishedInpq:P2322?articleId.}OPTIONAL{?itemwdt:P953?url.}OPTIONAL{?itemwdt:P577?date.}FILTER(?date>="1996-03-01T00:00:00"^^xsd:dateTime)FILTER(!BOUND(?articleId))SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".}}
# journalist strings by number of articles# So we can know who to create# Improvement: join with existing journalist label! Boom!SELECTDISTINCT(COUNT(?item)AS?count)?authorStringWHERE{?itemwdt:P1433wd:Q164746.hint:Priorhint:runFirst"true"^^xsd:boolean.?item(wdt:P31/(wdt:P279*))wd:Q191067;wdt:P2093?authorString.}GROUP BY?authorStringORDER BYDESC(?count)
# WSJ articles' main subjects order by count# and the winner is... OPEC!SELECTDISTINCT(COUNT(?item)AS?count)?subject$subjectLabelWHERE{?itemwdt:P1433wd:Q164746.hint:Priorhint:runFirst"true"^^xsd:boolean.?item(wdt:P31/(wdt:P279*))wd:Q191067;wdt:P921?subject.SERVICEwikibase:label{bd:serviceParamwikibase:language"en,fr".}}GROUP BY?subject$subjectLabelORDER BYDESC(?count)
# daily newspapers by number of articles in WDSELECT(COUNT($q)AS$count)$publisher$publisherLabelWHERE{$qwdt:P1433$publisher.$publisherwdt:P31wd:Q1110794.SERVICEwikibase:label{bd:serviceParamwikibase:language"en,fr".}}GROUP BY?publisher$publisherLabelORDER BYDESC($count)
# list of portraits with author and subjectSELECT?item?itemLabel?author?authorLabel?subject?subjectLabelWHERE{?itemwdt:P361wd:Q30091381.hint:Priorhint:runFirst"true"^^xsd:boolean.?itemwdt:P50?author.OPTIONAL{?itemwdt:P921?subject.}SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".}}
# 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?obituaryLabelWHERE{?qwdt:P31wd:Q5;wdt:P1343?obituary.?obituarywdt:P31wd:Q309481;wdt:P1433wd:Q9684.?obituarywdt:P577?obi_date.OPTIONAL{?qwdt:P570?death_date}.FILTER(BOUND(?death_date)).# FILTER(!BOUND(?death_date)) will filter only the ones who have no date of death ?obituaryrdfs: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).SERVICEwikibase:label{bd:serviceParamwikibase:language"en,fr".}}GROUP BY?q?qLabel?death_date?obi_date?diff?obituary?obituaryLabelORDER BYDESC(?diff)
# an other query to check errors. with occupation to check if same person. SELECT?q?qLabel?occLabel?publicationDate?mainSubjectWHERE{?qwdt:P31wd:Q309481;wdt:P1433wd:Q9684.?qwdt:P577?publicationDate.?qwdt:P921?mainSubject.?mainSubjectwdt:P106?occ.# OPTIONAL { ?mainSubject wdt:P570 ?mainSubjectDeathDate . }FILTER(YEAR(?publicationDate)=2015).SERVICEwikibase:label{bd:serviceParamwikibase:language"[AUTO_LANGUAGE],en,fr,nl".}}
# number of NYT obituaries per yearSELECT?year(COUNT(?q)AS?count)WHERE{SELECTDISTINCT?q(YEAR(?date)AS?year)(MONTH(?date)AS?month)WHERE{?qwdt:P31wd:Q309481;wdt:P1433wd:Q9684.?qwdt:P577?date.FILTER(?date>="2006-01-01"^^xsd:dateTime&&?date<"2024-01-01"^^xsd:dateTime).}}GROUP BY?yearORDER BY?year
# items that have old akadem but not new akademSELECT?q?qLabel?akadem_old?akadem_newWHERE{?qwdt:P5378?akadem_old.OPTIONAL{?qwdt:P12214?akadem_new}.FILTER(!BOUND(?akadem_new)).SERVICEwikibase:label{bd:serviceParamwikibase:language"fr,en".}}