User:TweetsFactsAndQueries/Queries/gender distribution in movie characters over time

Originally posted on Twitter.

# 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

Try it!

Auxiliary query for all trans characters on Wikidata:

SELECT ?characterLabel ?movie ?movieLabel (YEAR(MIN(?publicationDate)) AS ?year) WHERE {
  ?movie wdt:P31/wdt:P279* wd:Q11424;
         wdt:P577 ?publicationDate;
         p:P161/pq:P453 ?character.
  { ?character wdt:P21 wd:Q2449503. } UNION
  { ?character wdt:P21 wd:Q1052281. } UNION
  { ?character wdt:P21/wdt:P279* wd:Q48270. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?characterLabel ?movie ?movieLabel

Try it!