Welcome!
This project called "When Was the Golden Era of Video Games" can be found on Datacamp's Project section in the Learn tab. In this project, I analyzed sales and consumer data of of video games released over the past 50 years. The goal here's to identify whether the popularity of video gamaes are on the rise or if the golden age of video games has already past?
Motivations
As a data analyst and lifelong video game enthusiasts this prompt caught my attention...
Features
The purpose of this project is demonstrate my knowledge of SQL. More specifically, this project features skills including data aggregation, filtering, Common Table Expressions(CTEs), and joining tables.
Video games are big business: the global gaming market is projected to be worth more than $300 billion by 2027 according to Mordor Intelligence. With so much money at stake, the major game publishers are hugely incentivized to create the next big hit. But are games getting better, or has the golden age of video games already passed?
In this project, you'll analyze video game critic and user scores as well as sales data for the top 400 video games released since 1977. You'll search for a golden age of video games by identifying release years that users and critics liked best, and you'll explore the business side of gaming by looking at game sales data.
Your search will involve joining datasets and comparing results with set theory. You'll also filter, group, and order data. Make sure you brush up on these skills before trying this project! The database contains two tables. Each table has been limited to 400 rows for this project, but you can find the complete dataset with over 13,000 games on Kaggle.
game_sales table
game_sales table| Column | Definition | Data Type |
|---|---|---|
| name | Name of the video game | varchar |
| platform | Gaming platform | varchar |
| publisher | Game publisher | varchar |
| developer | Game developer | varchar |
| games_sold | Number of copies sold (millions) | float |
| year | Release year | int |
reviews table
reviews table| Column | Definition | Data Type |
|---|---|---|
| name | Name of the video game | varchar |
| critic_score | Critic score according to Metacritic | float |
| user_score | User score according to Metacritic | float |
users_avg_year_rating table
users_avg_year_rating table| Column | Definition | Data Type |
|---|---|---|
| year | Release year of the games reviewed | int |
| num_games | Number of games released that year | int |
| avg_user_score | Average score of all the games ratings for the year | float |
critics_avg_year_rating table
critics_avg_year_rating table| Column | Definition | Data Type |
|---|---|---|
| year | Release year of the games reviewed | int |
| num_games | Number of games released that year | int |
| avg_critic_score | Average score of all the games ratings for the year | float |
Ten Best-Selling Games
This query shows all data for the ten games that sold the most units.
Insights
- Leading with twice the total sales of the second, the best-selling game is Wii Sports for Wii.
- Among records from 1977-2020, there are 8 records in the top ten selling games between 2006-2017.
- 7 between 2006-2012 with an outlier, PLAYERUNKNOWN'S BATTLEGROUNDS for PC in 2017.
- The most featured publisher in the in top 10 is Nintendo with 7 games.
- Wii is the most featured platform.
- According to this data, the most popular video game franchise is Super Mario Bros. with 4 games.
-- best_selling_games
WITH best_ten_selling_games AS (
SELECT *
FROM game_sales
ORDER BY games_sold DESC
LIMIT 10
)
SELECT *
FROM best_ten_selling_games;Ten Years with the Highest Average Critic Score
This query displays the top ten years for video game releases according to the average critic score per year, the number of games released, and the average critic score for that year; each with at least 4 releases.
Insights
- The highest rated years among critics cluster between 1998-2004, and a second between 2011-2017.
- According to the ranking, in the top half of years there were around 10 games released; the bottom half either exceeded or doubled.
- It is likely that with developers focusing on fewer projects they produce higher quality products.
- The highest rated year among year among critics is 1998 whichis featured in the first query with it's top selling game, the renowned Pokemon Red / Green / Blue Version for GB.
-- critics_top_ten_years
WITH critics_top_ten_years AS (
SELECT year, num_games, avg_critic_score
FROM critics_avg_year_rating
WHERE num_games >= 4
ORDER BY avg_critic_score DESC
LIMIT 10
)
SELECT *
FROM critics_top_ten_yearsTop Years Agreed Among Critics and Users
The following query shares the years that received at least one average score between critics and users over 9. In addition, it shows the number of games released, the scores of critics and users respectively, and the calculated difference between the two.
Insights
- The greatest year for video games, agreed upon by both critic and users is 1998.
- This is the only year with both an average critic and user scores above 9.
- Generally, critics scores are lower than user scores.
Note for Further Examination
According to this exploratory analysis of the data, it is irregular for two top years to follow. However, this query shows 2009-2010 to be a notable time for both critics and users.
-- golden_years
WITH golden_years AS (
SELECT users.year,
users.num_games,
critics.avg_critic_score,
users.avg_user_score,
ABS(avg_user_score - avg_critic_score) AS diff
FROM public.users_avg_year_rating AS users
FULL JOIN public.critics_avg_year_rating AS critics
ON users.year = critics.year
WHERE avg_critic_score > 9
OR avg_user_score > 9
ORDER BY diff
)
SELECT *
FROM golden_years
Conclusion
The golden era of video games seems to be between the late 90's and 2000's. This period is the most notable spike by criticism, however the number of games released per year is increasing. With the number of products in the market on the rise, Nintendo has been recognized as a lead publisher for over 30 years and has continued to produce highly rated products in recent years.
Aside from the above mentioned milestones, there have been other notable products and performances; the next big hit may come anyday from anywhere. The global gaming market has experienced a range of peaks and it appears it'll continue to do so.
Make your game! It could be the next hit.