User:TweetsFactsAndQueries/Queries/gender distribution in movie characters over time
Jump to navigation
Jump to search
Try it! Try it!
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
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