Cappuccino.svg

Metabase Meet & Greet

Meet the WMSE team behind Metabase and other editors and friends of Metabase! Everyone is welcome, regardless if you are an active editor or simply interested in the platform. Ask questions, brainstorm and write queries, or simply chat about the future of Metabase.

The first Metabase Meet & Greet will be in November - help us pick the time.

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!

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!


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!


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

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!

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

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