1. Dataset Background

This dataset contains a list of video games with sales greater than 100,000 copies (Kaggle.com). Fields are:

2. Business Questions

I used R and SQL to solve the following six business questions using the dataset:

  1. Top Publishers by Global Sales:
    • Identify the top 5 publishers based on total global sales. What percentage of the market share do these top publishers represent?
  2. Platform Popularity Over Time:
    • Determine the most popular gaming platform for each year. Has there been a shift in platform popularity over the years?
  3. Genre Preference by Country:
    • Analyze genre preferences in North America, Europe, and Japan. Were there significant differences in the preferred game genres across these regions?
  4. Platform Lifecycle Analysis:
    • Explore the lifecycle of gaming platforms. Identify the average lifespan of a platform and investigate the sales pattern from the platform’s initial to latest game launch.
  5. Sales Pattern of One Platform:
    • Investigate the sales pattern from one platform’s initial to latest game launch.
  6. Publisher Performance Over Time:
    • Evaluate the performance of specific publishers over time. Were there publishers that have consistently performed well, or are there notable fluctuations?

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(reactable)
library(grid)
library(DBI)
library(dbplyr)
library(ggtext)

#Load the dataset
vgsales <- readr::read_csv("~/Desktop/R Studio/sql/data/vgsales.csv") |> 
  janitor::clean_names() #un-capitalize column names

#Create empty database and connect it to the dataset
mydb = dbplyr::src_memdb()$con
dplyr::copy_to(mydb, vgsales)

#check the dataset
dplyr::tbl(src = mydb, "vgsales")

Business Question 1

Identify the top 5 publishers based on total global sales. What percentage of the market share do these top publishers represent?

--Create CTE to identify the top 5 publishers based on total global sales.
WITH TopPublishers AS (
    SELECT 
      publisher,
      SUM(global_sales) AS total_global_sales
    FROM 
      vgsales
    GROUP BY 
      publisher
    ORDER BY 
      total_global_sales DESC
    LIMIT 5
)
--Main query to calculate percentage of the market share represented by the top 5 publishers.
SELECT
  tp.publisher,
  tp.total_global_sales,
  ROUND(tp.total_global_sales / total_market.all_global_sales * 100, 2) AS market_share_percentage
FROM 
  TopPublishers AS tp
JOIN (
    SELECT
      SUM(global_sales) AS all_global_sales
    FROM 
      vgsales
     ) total_market ON 1=1

Answer: Nintendo, EA, Activision, Sony and Ubisoft are the top 5 publishers, in order of high to low total global sales in millions and market share percentage in %, since each platform’s initial game release.

Here are two bar graphs illustrating the publishers’ global sales and market share percentage.

Top 5 Publishers by Global Sales

Top 5 Publishers by Market Share Percentage

Business Question 2

Determine the most popular gaming platform for each year. Has there been a shift in platform popularity over the years?

--Create CTE
WITH PlatformPopularity AS (
    SELECT
      year,
      platform,
      SUM(global_sales) AS total_sales,
      RANK() OVER(PARTITION BY year ORDER BY SUM(global_sales) DESC) AS platform_rank
      --the 'PARTITION BY' ranks global_sales separately for each year
FROM 
  vgsales
GROUP BY
  year, platform)
  
--Main Query 
SELECT 
  year,
  platform,
  total_sales
FROM 
  PlatformPopularity
WHERE 
  platform_rank = 1 AND
  year <> 'N/A'
ORDER BY year DESC

Answer: Platform popularity was determined by total global sales made in millions.From1980 to 2020, different platforms were popular, with different versions of PS being more over the years than other platforms.

Use this table to search for any year and platform!

Business Question 3

Analyze genre preferences in North America, Europe, and Japan. Were there significant differences in the preferred game genres across these regions?

I used the following code to answer to the question:

SELECT 
  genre,
  SUM(eu_sales) AS total_eu_sales,
  SUM(jp_sales) AS total_japan_sales,
  SUM(na_sales) AS total_na_sales
FROM
  vgsales
GROUP BY 
  genre
ORDER BY total_eu_sales DESC, total_japan_sales DESC, total_na_sales DESC
Displaying records 1 - 10
genre total_eu_sales total_japan_sales total_na_sales
Action 525.00 159.95 877.83
Sports 376.85 135.37 683.35
Shooter 313.27 38.28 582.60
Racing 238.39 56.69 359.42
Misc 215.98 107.76 410.24
Platform 201.63 130.77 447.05
Role-Playing 188.06 352.31 327.28
Simulation 113.38 63.70 183.31
Fighting 101.32 87.35 223.59
Adventure 64.13 52.07 105.80

Answer: Yes, there were differences in preferred game genres, between Europe/North America and Japan. I used sales made in each country as a proxy of each country’s game genre preference. For Europe and North America, Action was the most preferred genre, sales measured in millions, followed by Sports, and Shooter. For Japan, Role-Playing, followed by Action and Sports were the most preferred genre.

Business Question 4

Explore the lifecycle of gaming platforms. Identify the average lifespan of a platform and investigate the sales pattern from the platform’s initial to latest game launch.

I used the following code to answer to the question:

SELECT
  platform,
  MIN(year) AS initial_year,
  MAX(year) AS latest_year,
  MAX(year) - MIN(year) + 1 AS platform_lifespan
FROM 
  vgsales
WHERE 
  NOT year = 'N/A'
GROUP BY
  platform
ORDER BY
  platform_lifespan DESC

Answer: I defined the lifespan of a gaming platform as the number of years between its initial and latest game launch. Different platforms had different average lifespan, ranging from minimum 1 to maximum 36 years 36 years.

This graph shows the platform DS having the highest average lifespan, followed by PC, GB, and X360.

Business Question 5

Investigate the sales pattern of one platform’s initial to latest game launch.

Answer: I first use this code to chose one platform. I chose DS because it has been in the market for the longest time, since 1985.

SELECT
  platform,
  year,
  SUM(global_sales) AS total_sales
FROM
  vgsales
WHERE 
  platform = 'DS' AND
  NOT year = 'N/A'
GROUP BY year
Displaying records 1 - 10
platform year total_sales
DS 1985 0.02
DS 2004 17.46
DS 2005 131.40
DS 2006 121.15
DS 2007 149.36
DS 2008 147.89
DS 2009 121.99
DS 2010 87.98
DS 2011 27.80
DS 2012 11.64

From its initial game launch, DS has experienced fluctuations in global sales, with the highest sales made in 2008 (147.89 millions) and the lowest sales made in 1985 and 2020 (0.29 millions).

Business Question 6

Evaluate the performance of specific publishers over time. Are there publishers that have consistently performed well, or are there notable fluctuations?

Answer: I first identified a publisher with the highest global sales and consequently chose Nintendo.

SELECT
  publisher,
  SUM(global_sales) AS global_sales
FROM
  vgsales
GROUP BY
  publisher
ORDER BY 
  global_sales DESC
Displaying records 1 - 10
publisher global_sales
Nintendo 1786.56
Electronic Arts 1110.32
Activision 727.46
Sony Computer Entertainment 607.50
Ubisoft 474.72
Take-Two Interactive 399.54
THQ 340.77
Konami Digital Entertainment 283.64
Sega 272.99
Namco Bandai Games 254.09

Then, I executed the following code to examine Nintedo’s sales performance for all the games that they launched since 1983. I calculated percent change to allow for an overview of how the publisher’s sales performance has fluctuated over the years.

SELECT
  year,
  name,
  SUM(global_sales) AS global_sales,
  LAG(SUM(global_sales)) OVER (ORDER BY year) AS previous_global_sales,
  ROUND((SUM(global_sales) - LAG(SUM(global_sales)) OVER ())/ LAG(SUM(global_sales)) OVER () *100, 2) AS perc_change
FROM
  vgsales
WHERE (publisher = 'Nintendo' AND
      NOT year = 'N/A')
GROUP BY
  year
ORDER BY
  year

Check out the graph to view how Nintendo’s global sales have fluctuated over the years.