User:TweetsFactsAndQueries/Queries/films starring more than one future head of government

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

Try it!