query-f86f853395be04e54eab9462cdbc0d84

rq turtle/ttl

gender distribution in movie characters over time SELECT * WITH { SELECT DISTINCT ?movie ?character WHERE { ?movie wdt:P31/wdt:P279 wd:Q11424; p:P161 ?castMemberStatement. { ?castMemberStatement pq:P453 ?character. } UNION { ?castMemberStatement ps:P161 ?character; pq:P453 wd:Q18086706. } } } AS %moviesWithCharacters WITH { SELECT ?movie (YEAR(MIN(?publicationDate)) AS ?year) WHERE { INCLUDE %moviesWithCharacters. ?movie wdt:P577 ?publicationDate. FILTER(!wikibase:isSomeValue(?publicationDate)) } GROUP BY ?movie } AS %moviesWithYears WITH { SELECT ?movie (COUNT(DISTINCT ?cisMaleCharacter) AS ?cisMaleCharacters) WHERE { INCLUDE %moviesWithCharacters. OPTIONAL { ?character wdt:P21 wd:Q6581097. BIND(?character AS ?cisMaleCharacter) } } GROUP BY ?movie } AS %moviesWithCisMaleCharacters WITH { SELECT ?movie (COUNT(DISTINCT ?cisFemaleCharacter) AS ?cisFemaleCharacters) WHERE { INCLUDE %moviesWithCharacters. OPTIONAL { ?character wdt:P21 wd:Q6581072. BIND(?character AS ?cisFemaleCharacter) } } GROUP BY ?movie } AS %moviesWithCisFemaleCharacters WITH { SELECT ?movie (COUNT(DISTINCT ?transMaleCharacter) AS ?transMaleCharacters) WHERE { INCLUDE %moviesWithCharacters. OPTIONAL { ?character wdt:P21 wd:Q2449503. BIND(?character AS ?transMaleCharacter) } } GROUP BY ?movie } AS %moviesWithTransMaleCharacters WITH { SELECT ?movie (COUNT(DISTINCT ?transFemaleCharacter) AS ?transFemaleCharacters) WHERE { INCLUDE %moviesWithCharacters. OPTIONAL { ?character wdt:P21 wd:Q1052281. BIND(?character AS ?transFemaleCharacter) } } GROUP BY ?movie } AS %moviesWithTransFemaleCharacters WITH { SELECT ?movie (COUNT(DISTINCT ?nonbinaryCharacter) AS ?nonbinaryCharacters) WHERE { INCLUDE %moviesWithCharacters. OPTIONAL { ?character wdt:P21/wdt:P279 wd:Q48270. BIND(?character AS ?nonbinaryCharacter) } } GROUP BY ?movie } AS %moviesWithNonbinaryCharacters WITH { SELECT ?year (SUM(?cisMaleCharacters) AS ?cisMaleCharacters_) (SUM(?cisFemaleCharacters) AS ?cisFemaleCharacters_) (SUM(?transMaleCharacters) AS ?transMaleCharacters_) (SUM(?transFemaleCharacters) AS ?transFemaleCharacters_) (SUM(?nonbinaryCharacters) AS ?nonbinaryCharacters_) WHERE { INCLUDE %moviesWithYears. INCLUDE %moviesWithCisMaleCharacters. INCLUDE %moviesWithCisFemaleCharacters. INCLUDE %moviesWithTransMaleCharacters. INCLUDE %moviesWithTransFemaleCharacters. INCLUDE %moviesWithNonbinaryCharacters. } GROUP BY ?year } AS %yearsWithCounts WHERE { INCLUDE %yearsWithCounts. BIND(?cisMaleCharacters_ + ?transMaleCharacters_ AS ?maleCharacters) BIND(?cisFemaleCharacters_ + ?transFemaleCharacters_ AS ?femaleCharacters) BIND(?cisMaleCharacters_ + ?cisFemaleCharacters_ AS ?cisCharacters) BIND(?transMaleCharacters_ + ?transFemaleCharacters_ + ?nonbinaryCharacters_ AS ?transCharacters) } ORDER BY ?year

Use at

PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX p: <http://www.wikidata.org/prop/>
# gender distribution in movie characters over time
SELECT *
WHERE {
   {
  SELECT ?year (SUM(?cisMaleCharacters) AS ?cisMaleCharacters_) (SUM(?cisFemaleCharacters) AS ?cisFemaleCharacters_) (SUM(?transMaleCharacters) AS ?transMaleCharacters_) (SUM(?transFemaleCharacters) AS ?transFemaleCharacters_) (SUM(?nonbinaryCharacters) AS ?nonbinaryCharacters_) WHERE {
     {
  SELECT ?movie (YEAR(MIN(?publicationDate)) AS ?year) WHERE {
    INCLUDE %moviesWithCharacters.
    ?movie wdt:P577 ?publicationDate.
    FILTER(!wikibase:isSomeValue(?publicationDate))
  }
  GROUP BY ?movie
}
     {
  SELECT ?movie (COUNT(DISTINCT ?cisMaleCharacter) AS ?cisMaleCharacters) WHERE {
    INCLUDE %moviesWithCharacters.
    OPTIONAL {
      ?character wdt:P21 wd:Q6581097.
      BIND(?character AS ?cisMaleCharacter)
    }
  }
  GROUP BY ?movie
}
     {
  SELECT ?movie (COUNT(DISTINCT ?cisFemaleCharacter) AS ?cisFemaleCharacters) WHERE {
    INCLUDE %moviesWithCharacters.
    OPTIONAL {
      ?character wdt:P21 wd:Q6581072.
      BIND(?character AS ?cisFemaleCharacter)
    }
  }
  GROUP BY ?movie
}
     {
  SELECT ?movie (COUNT(DISTINCT ?transMaleCharacter) AS ?transMaleCharacters) WHERE {
    INCLUDE %moviesWithCharacters.
    OPTIONAL {
      ?character wdt:P21 wd:Q2449503.
      BIND(?character AS ?transMaleCharacter)
    }
  }
  GROUP BY ?movie
}
     {
  SELECT ?movie (COUNT(DISTINCT ?transFemaleCharacter) AS ?transFemaleCharacters) WHERE {
    INCLUDE %moviesWithCharacters.
    OPTIONAL {
      ?character wdt:P21 wd:Q1052281.
      BIND(?character AS ?transFemaleCharacter)
    }
  }
  GROUP BY ?movie
}
     {
  SELECT ?movie (COUNT(DISTINCT ?nonbinaryCharacter) AS ?nonbinaryCharacters) WHERE {
    INCLUDE %moviesWithCharacters.
    OPTIONAL {
      ?character wdt:P21/wdt:P279* wd:Q48270.
      BIND(?character AS ?nonbinaryCharacter)
    }
  }
  GROUP BY ?movie
}
  }
  GROUP BY ?year
}
  BIND(?cisMaleCharacters_ + ?transMaleCharacters_ AS ?maleCharacters)
  BIND(?cisFemaleCharacters_ + ?transFemaleCharacters_ AS ?femaleCharacters)
  BIND(?cisMaleCharacters_ + ?cisFemaleCharacters_ AS ?cisCharacters)
  BIND(?transMaleCharacters_ + ?transFemaleCharacters_ + ?nonbinaryCharacters_ AS ?transCharacters)
}
ORDER BY ?year

Query found at