Project:SPARQL/examples

From MetaBase
Jump to navigation Jump to search

Overview queries

All types of documents and their Wikidata equivalents

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?wikidata_iri
WHERE
{
?item wbt:P4* wb:Q21.
OPTIONAL {?item wbt:P1 ?wikidataQ}
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


All types of activities and events and their Wikidata equivalents

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?wikidata_iri
WHERE
{
?item wbt:P4* wb:Q8.
OPTIONAL {?item wbt:P1 ?wikidataQ}
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


All index terms and their Wikidata equivalents

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabelEN ?itemLabelSV ?wikidata_iri
WHERE
{
?item wbt:P5 wb:Q12 .
OPTIONAL {?item wbt:P1 ?wikidataQ}
OPTIONAL {?item rdfs:label ?itemLabelEN filter (lang(?itemLabelEN) = "en")}.
OPTIONAL {?item rdfs:label ?itemLabelSV filter (lang(?itemLabelSV) = "sv")}.
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)
}

Try it!

All properties and items with labels and descriptions in English and Swedish

PREFIX wd: <https://metabase.wikibase.cloud/entity/>
PREFIX wdt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabelEN ?itemLabelSV ?itemDescEN ?itemDescSV
WHERE
{
  OPTIONAL {?item rdfs:label ?itemLabelEN filter (lang(?itemLabelEN) = "en")}.
  OPTIONAL {?item rdfs:label ?itemLabelSV filter (lang(?itemLabelSV) = "sv")}.
  OPTIONAL {?item schema:description ?itemDescEN filter (lang(?itemDescEN) = "en")}.
  OPTIONAL {?item schema:description ?itemDescSV filter (lang(?itemDescSV) = "sv")}.
}

Try it!

Content uploads

All content uploads

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel ?commonscat
WHERE
{
?item wbt:P5 wb:Q22186 .
OPTIONAL {?item wbt:P37 ?commonscat}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,sv,[AUTO_LANGUAGE]". }
}

Try it!


All resource types

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{
?item wbt:P5 wb:Q22211 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,[AUTO_LANGUAGE]". }
}

Try it!


Documents

Documents in languages other than Swedish

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?language ?languageLabel
WHERE
{
?item wbt:P5/wbt:P4* wb:Q21.
?item wbt:P20 ?language.
MINUS {?item wbt:P20 wb:Q31}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Documents published in 2020

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?year
WHERE
{
?item wbt:P5/wbt:P4* wb:Q21.
?item wbt:P18 ?published.
BIND(str(YEAR(?published)) AS ?year)
FILTER (?year = "2020")
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


People and how many documents they have authored

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?author ?authorLabel (COUNT(?item) AS ?count)
WHERE
{
?item wbt:P5/wbt:P4* wb:Q21.
?item wbt:P19 ?author.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
GROUP BY ?author ?authorLabel
ORDER BY DESC (?count)

Try it!


Topics

Timeline of everything about a topic

#defaultView:Timeline
PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?time
WHERE
{
?item wbt:P15 wb:Q90 # SDC
{?item wbt:P23 ?time} # point in time
UNION
{?item wbt:P18 ?time} # publication date
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,sv". }
}

Try it!

Topics of the sessions at the GLAM Wiki 2023 conference

#defaultView:BubbleChart
PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?mainsubject ?mainsubjectLabel (COUNT(?item) AS ?count)
WHERE
{
?item wbt:P17 wb:Q22660.
?item wbt:P15 ?mainsubject
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?mainsubject ?mainsubjectLabel
ORDER BY DESC(?count)

Try it!


Events

Most common locations for WMSE events

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
#defaultView:BubbleChart
SELECT ?location ?locationLabel (COUNT(DISTINCT ?item) AS ?count)
WHERE
{
?item wbt:P5/wbt:P4* wb:Q40 .
?item wbt:P27 ?location.
?item wbt:P14 wb:Q9.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
GROUP BY ?location ?locationLabel
ORDER BY DESC(?count)

Try it!


Number of events 2021-2022 and total number of participants

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT (count(distinct ?item) as ?total_events) (sum(?participants) as ?total_participants)
WHERE
{
  ?item wbt:P23 ?when.
  FILTER (?when > "2021-01-01"^^xsd:dateTime && ?when < "2022-12-31"^^xsd:dateTime)
  OPTIONAL{?item wbt:P28 ?participants.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Video recordings of events of Wikimedia Sverige

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?video
WHERE
{
  ?item wbt:P14 wb:Q9.
  ?item wbt:P31 ?video.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Presentations without a speaker

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{?item wbt:P5/wbt:P4* wb:Q48.
MINUS {?item wbt:P33 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Workshops without a leader

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{?item wbt:P5 wb:Q37.
MINUS {?item wbt:P32 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Projects

Timeline of all projects

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
#defaultView:Timeline
SELECT ?item ?itemLabel ?start ?end
WHERE
{
?item wbt:P5 wb:Q2 .
?item wbt:P6 ?start.
?item wbt:P7 ?end.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Most common programmatic areas for WMSE projects

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?program ?programLabel (COUNT(DISTINCT ?item) AS ?count)
WHERE
{
?item wbt:P5 wb:Q2 .
?item wbt:P14 wb:Q9.
?item wbt:P10 ?program.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
GROUP BY ?program ?programLabel
ORDER BY DESC(?count)

Try it!


Federated queries

Everything that took place in a certain country

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item ?itemLabel ?place ?placeLabel
WHERE
{
?item wbt:P27 ?place.
?place wbt:P1 ?wikidataQ.
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)

SERVICE <https://query.wikidata.org/sparql> {
?wikidata_iri wdt:P17 wd:Q34. # Sweden
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
ORDER BY ?placeLabel

Try it!

Map of all event locations

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
#defaultView:Map
SELECT DISTINCT ?item ?itemLabel ?place ?placeLabel ?coords
WHERE
{
?item wbt:P5/wbt:P4* wb:Q40 .
?item wbt:P27 ?place.
?place wbt:P1 ?wikidataQ.
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)

SERVICE <https://query.wikidata.org/sparql> {
?wikidata_iri wdt:P625 ?coords.
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Documents authored by employees of Wikimedia Sverige (via Wikidata)

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?document ?documentLabel ?person ?personLabel
WHERE
{
?document wbt:P19 ?person.
?person wbt:P1 ?wikidataQ.

BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)

SERVICE <https://query.wikidata.org/sparql> {
?wikidata_iri wdt:P108 wd:Q15279144.
}

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

Try it!


Organizations and their social media and official website (from Wikidata)

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel ?wikidata_iri ?url ?twitter ?instagram
WHERE
{
?item wbt:P5 wb:Q13 .
?item wbt:P1 ?wikidataQ.

BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)

SERVICE <https://query.wikidata.org/sparql> {
 OPTIONAL {?wikidata_iri wdt:P856 ?url.}
 OPTIONAL {?wikidata_iri wdt:P2002 ?twitter.}
 OPTIONAL {?wikidata_iri wdt:P2003 ?instagram.}
}

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

Try it!


Maintenance queries

Root items requiring Wikidata equivalent without Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{?item wbt:P5 wb:Q10.
MINUS {?item wbt:P1 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Events (excluding campaigns, photo contests) without indication whether they're online, in-person or hybrid

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item ?itemLabel
WHERE
{
?item wbt:P5/wbt:P4* wb:Q40 . # is an event
MINUS {?item wbt:P5 wb:Q81} # is not an online event
MINUS {?item wbt:P5 wb:Q80} # is not an in-person event
MINUS {?item wbt:P5 wb:Q79} # is not a hybrid event
MINUS {?item wbt:P5 wb:Q22217} # is not a campaign
MINUS {?item wbt:P5 wb:Q142} # is not a photo contest
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Organizations without Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{
?item wbt:P5 wb:Q13 .
MINUS {?item wbt:P1 [].}

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

Try it!

Items used as values of Main Subject that are not Index Terms

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel ?subject ?subjectLabel
WHERE
{
?item wbt:P15 ?subject.
FILTER NOT EXISTS {?subject wbt:P5 wb:Q12}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Items with non-unique Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item1 ?item1Label ?item2 ?item2Label ?value WHERE {
  ?item1 wbt:P1 ?value.
  ?item2 wbt:P1 ?value.

  FILTER((?item1 != ?item2) && ((STR(?item1)) < (STR(?item2))))
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv". }
}

Try it!

Properties without usage example

PREFIX wd: <https://metabase.wikibase.cloud/entity/>
PREFIX wdt: <https://metabase.wikibase.cloud/prop/direct/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT ?item ?itemLabel
WHERE
{
  ?item rdf:type wikibase:Property.
  MINUS {?item wdt:P30 []}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Index terms without Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE {
?item wbt:P5 wb:Q12.
MINUS {?item wbt:P1 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Organizers who are not Organizations or Persons

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item ?itemLabel ?organizer ?organizerLabel
WHERE {
?item wbt:P14 ?organizer.
?organizer wbt:P5 ?organizerType.
FILTER(?organizerType NOT IN(wb:Q13, wb:Q23) ). # organizer type is not organization or person

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

Try it!

All Temporary Free Text Fields

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
PREFIX wbp: <https://metabase.wikibase.cloud/prop/>
PREFIX wbpq: <https://metabase.wikibase.cloud/prop/qualifier/>
SELECT DISTINCT ?item ?itemLabel ?val ?qual ?qualLabel
WHERE
{
?item wbt:P38 ?val.
OPTIONAL {?item wbp:P38 ?statement.
?statement wbpq:P41 ?qual .}
     
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Temporary Free Text Fields without a qualifier saying which property they should be replaced with

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
PREFIX p:<https://metabase.wikibase.cloud/prop/>
PREFIX pq:<http://https://metabase.wikibase.cloud/prop/qualifier/>

SELECT ?item ?itemLabel
WHERE
{
	?item p:P38 ?statement .
  	VALUES ?pq { pq:P38 } .
	OPTIONAL {
		?statement ?pq ?qualif .
	} .
	FILTER( !BOUND( ?qualif ) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Subjects of Wikimedia Platforms Affected that are not Wikimedia Content Projects or Family of Wikimedia Projects

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item ?itemLabel ?affectedPlatform ?affectedPlatformLabel
WHERE
{
?item wbt:P13 ?affectedPlatform.
?affectedPlatform wbt:P5 ?what.
 
MINUS
{
?affectedPlatform wbt:P5 wb:Q22.
}

MINUS
{
?affectedPlatform wbt:P5 wb:Q25.
}

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

Try it!

Presentations without Part of

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{?item wbt:P5/wbt:P4* wb:Q48.
MINUS {?item wbt:P17 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Presentations that are Part of something that is not an activity

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel ?partOfInstance ?partOfInstanceLabel
WHERE
{?item wbt:P5/wbt:P4* wb:Q48.
?item wbt:P17 ?partOf
MINUS {?partOf wbt:P5/wbt:P4* wb:Q8}
?partOf wbt:P5 ?partOfInstance.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Retrieved from "https://metabase.wikibase.cloud/w/index.php?title=Project:SPARQL/examples&oldid=18206"