User:TweetsFactsAndQueries/Queries/films starring more than one future head of government
Jump to navigation
Jump to search
Try it!
Originally posted on Twitter.
SELECT ?film ?filmLabel ?filmDescription (COUNT(DISTINCT ?headOfGovernmentLabel) AS ?count) (GROUP_CONCAT(DISTINCT CONCAT(?headOfGovernmentLabel, ", "@en, ?description); separator = "; ") AS ?headsOfGovernment)
WITH {
SELECT ?headOfGovernment ?position (MIN(?startTime) AS ?earliestStartTime) WHERE {
?headOfGovernment wdt:P31 wd:Q5;
p:P39 [
ps:P39 ?position;
pq:P580 ?startTime
].
?position wdt:P279+ wd:Q2285706.
}
GROUP BY ?headOfGovernment ?position
} AS %headsOfGovernment
WHERE {
INCLUDE %headsOfGovernment.
?film wdt:P31/wdt:P279* wd:Q11424;
wdt:P577 ?publicationDate;
p:P161 ?headOfGovernmentStatement.
?headOfGovernmentStatement ps:P161 ?headOfGovernment.
OPTIONAL { ?headOfGovernmentStatement pq:P453 ?character. ?character rdfs:label ?characterLabel. FILTER(LANG(?characterLabel) = "en") } # label service doesn’t work for this one for some reason – perhaps the query optimizer is being stupid and moving the label service before this optional block, so that ?character isn’t bound yet when the label service runs
FILTER(?earliestStartTime > ?publicationDate) # *future* head of government
FILTER NOT EXISTS {
?headOfGovernment p:P39 [
ps:P39 ?otherPosition;
pq:P580 ?otherStartTime
].
?otherPosition wdt:P279+ wd:Q2285706.
FILTER(?otherStartTime < ?publicationDate) # not already a head of government (some people held multiple “head of government” offices, or the same one multiple times)
}
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
?film rdfs:label ?filmLabel;
schema:description ?filmDescription.
?headOfGovernment rdfs:label ?headOfGovernmentLabel.
?position rdfs:label ?positionLabel.
} hint:Prior hint:runLast false.
BIND(IF(BOUND(?characterLabel), CONCAT(?characterLabel, " / "@en, ?positionLabel), ?positionLabel) AS ?description)
}
GROUP BY ?film ?filmLabel ?filmDescription
HAVING(?count > 1)
ORDER BY DESC(?count) ASC(MIN(?publicationDate))