1. Dataset Background

There are data on 64 videogame releases between 2012 and 2022 (Kaggle.com).

2. Business Questions

I used R and SQL to solve the following four business questions. There are three datasets in total; I used two datasets and focused my analysis on the data from 2022:

  1. Gender Representation over Time in Video Games:
    • What is the proportion of character genders in video games? Can we identify any trends over time?
  2. Character Role Representation in Video Games:
    • How are genders represented in different character roles (protagonists, antagonists, sidekicks, etc.)? Are there any biases?
  3. Gender Reoresentations across Game Genres:
    • Which game genres have more diverse gender representation? Are certain genres dominated by specific genders?
  4. Marketing Strategies related to Age Representation and Game Sales:
    • Can we identify successful marketing strategies related to gender representation? How does inclusive marketing affect game sales?

3. Data Analysis

Data setup and cleaning

I set up the R environment with necessary libraries, then cleaned the dataset, and set up the SQL environment within R.

# Load library
library(tidyverse)
library(DBI)
library(dbplyr)
library(dplyr)

# Load datasets
characters <- readr::read_csv("~/Desktop/R Studio/sql/videogame representation/characters.csv") |> 
  janitor::clean_names() #un-capitalize column names

games <- readr::read_csv("~/Desktop/R Studio/sql/videogame representation/games.csv") |> 
  janitor::clean_names() |> 
  mutate(release_month = lubridate::month(release))

# Create empty database and connect it to the dataset
mydb = src_memdb()$con

# Connect each dataset to the database
character <- copy_to(mydb, characters, name = "character",
                 temporary = FALSE, 
                 overwrite = TRUE)
game <- copy_to(mydb, games, name = "game", 
                 temporary = FALSE, 
                 overwrite = TRUE)

#check the dataset
tbl(src = mydb, "character") |> head(15) #show 15 rows
tbl(src = mydb, "game") |> head(15) #show 15 rows

Business Question 1

Gender Distribution: What is the proportion of character genders in video games? Can we identify any trends over time?

-- CTE to calculate character counts by release month and gender
WITH df AS (
    SELECT 
        g.release_month,
        c.gender,
        COUNT(c.gender) AS character_count
    FROM 
        game g
    JOIN 
        character c ON g.game_id = c.game
    GROUP BY 
        g.release_month, c.gender
    ORDER BY 
        g.release_month
)

--Main query to calculate the proportion of each gender for each month
SELECT 
    release_month,
    gender,
    character_count,
    SUM(character_count) OVER (PARTITION BY release_month) AS total_character_count,
    ROUND(
          CAST(character_count AS float) / 
          CAST(SUM(character_count) OVER (PARTITION BY release_month) AS float), 2
    ) * 100
    AS proportion
FROM 
    df;

Answer: In 2022, male characters dominated the market, showing the highest proportion consistently across twelve months, followed by female characters. Non-binary and customer gender characters consistently appeared less in video games.

Business Question 2

How are genders represented in different character roles (protagonists, antagonists, sidekicks, etc.)? Are there any biases?

--CTE to calculate role counts for each gender
WITH df AS (
SELECT relevance as role,
       gender,
       COUNT(*) as character_count
FROM character
GROUP BY
  gender, relevance)

--Main query to calculate the proportion of roles for each gender
SELECT 
    df.*,
    SUM(character_count) OVER (PARTITION BY gender) AS total_character_count,
    ROUND(
          CAST(character_count AS FLOAT) / 
          CAST(SUM(character_count) OVER (PARTITION BY gender) AS FLOAT), 
          2
    ) * 100 
    AS proportion
FROM 
    df;

Answer: Female and male characters showed similar role distributions, such that they occupied secondary and main character roles the most. Non-binary characters played protagonist and secondary characters the most. Customer characters only occupied protagonist roles.

Business Question 3

Which game genres have more diverse gender representation? Are certain genres dominated by specific genders?

--CTE
WITH df AS (
SELECT g.genre,
       c.gender,
       COUNT(c.gender) AS gender_count
FROM character c
LEFT JOIN game g
ON c.game = g.game_id 
GROUP BY 
  g.genre, c.gender
)

--Main query 
SELECT *,
       SUM(gender_count) OVER (PARTITION BY genre) AS total_gender,
       ROUND(
             CAST(gender_count AS FLOAT)/
             CAST(SUM(gender_count) OVER (PARTITION BY genre) AS FLOAT), 2
       ) * 100
       AS proportion
FROM df;

Answer: For all six genres, male characters appared the most, followed by female characters, non-binary, and customer characters, if applicable. While this pattern applies to all genres, it is more pronounced in Action, Action-adventure, Interactive Story, and Simulation genres.

Business Question 4

Can we identify successful marketing strategies related to age representation? How does inclusive marketing affect game sales?

--CTE to join 'age range' and 'criteria' variables into one dataset
WITH df AS (
SELECT g.criteria,
       c.age_range,
       COUNT(c.age_range) AS age_range_count
FROM character c
LEFT JOIN game g
ON c.game = g.game_id
GROUP BY 
  g.criteria, c.age_range
)

--Main query to calculate distributions of age range for each 'criteria'
SELECT *,
       SUM(age_range_count) OVER (PARTITION BY criteria) AS total_age_range_count,
       ROUND(
              CAST(age_range_count AS FLOAT)/
              CAST(SUM(age_range_count) OVER (PARTITION BY criteria) AS FLOAT), 2
       ) * 100
       AS proportion --value in %
FROM df;

Answer: Games that are most sold, most sold & top rated, and top rated top featured characters who were adult and we can infer there is some correlation between the presence of adult characters and game sales.

Below shows graphs that speak to each business question.

Question 1

Question 2

Question 3

Question 4