When was the Golden Age of Video Games (SQL)

Introduction

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?

I'll compare a dataset on game sales with critics and user reviews to determine whether or not video games have improved as the gaming market has grown.

In this project, and using SQL, I'll explore the top 400 best-selling video games created between 1977 and 2020.

You can check the notebook on Github by clicking on the button below:

1- The 10 Best-Selling Games

This database contains two tables. Each table is limited to 400 rows for this project, but there is a complete dataset with over 13,000 games on Kaggle.

The game_sales table:

The reviews table:

Let's begin by looking at some of the top selling video games of all time!

-- Select all information for the top ten best-selling games

Select *

From game_sales

-- Order the results from best-selling game down to tenth best-selling

Order by games_sold desc

Limit 10;

2- Missing Review Scores

Wow, the best-selling video games were released between 1985 to 2017! That's quite a range; we'll have to use data from the reviews table to gain more insight into the best years for video games.

First, it's important to explore the limitations of our database. One big shortcoming is that there are not any reviews data for some of the games on the game_sales table.

-- Join games_sales and reviews

-- Select a count of the number of games where both critic_score and user_score are null

Select count(game)

From game_sales

Left Join reviews

Using(game)

Where reviews.critic_score is null and reviews.user_score is null;

3- Years that Video Game Critics Loved

It looks like a little less than ten percent of the games on the game_sales table don't have any reviews data. That's a small enough percentage that we can continue our exploration, but the missing reviews data is a good thing to keep in mind as we move on to evaluating results from more sophisticated queries.

There are lots of ways to measure the best years for video games! Let's start with what the critics think.

-- Select release year and average critic score for each year, rounded and aliased

Select year, round(avg(critic_score), 2) as avg_critic_score

From game_sales

-- Join the game_sales and reviews tables

Inner Join reviews

Using(game)

-- Group by release year

Group by year

-- Order the data from highest to lowest avg_critic_score and limit to 10 results

Order by avg_critic_score desc

Limit 10;

4- Was 1982 Really that Great?

The range of great years according to critic reviews goes from 1982 until 2020: we are no closer to finding the golden age of video games!

Hang on, though. Some of those avg_critic_score values look like suspiciously round numbers for averages. The value for 1982 looks especially fishy. Maybe there weren't a lot of video games in our dataset that were released in certain years.

Let's update our query and find out whether 1982 really was such a great year for video games.

-- Paste your query from the previous task; update it to add a count of games released in each year called num_games

-- Update the query so that it only returns years that have more than four reviewed games

Select year, count(game_sales.game) as num_games, round(avg(critic_score), 2) as avg_critic_score

From game_sales

Inner Join reviews

Using(game)

Group by year

Having count(reviews.game) > 4

Order by avg_critic_score desc

Limit 10;

5- Years that Dropped Off the Critics' Favorites List

That looks better! The num_games column convinces us that our new list of the critics' top games reflects years that had quite a few well-reviewed games rather than just one or two hits. But which years dropped off the list due to having four or fewer reviewed games? Let's identify them so that someday we can track down more game reviews for those years and determine whether they might rightfully be considered as excellent years for video game releases!

It's time to brush off your set theory skills. To get started, we've created tables with the results of our previous two queries:

The top_critic_years table:

The top_critic_years_more_than_four_games table:

-- Select the year and avg_critic_score for those years that dropped off the list of critic favorites

Select year, avg_critic_score

From top_critic_years

Except

Select year, avg_critic_score

From top_critic_years_more_than_four_games

-- Order the results from highest to lowest avg_critic_score

Order by avg_critic_score desc;

6- Years Video Game Players Loved

Based on our work in the task above, it looks like the early 1990s might merit consideration as the golden age of video games based on critic_score alone, but we'd need to gather more games and reviews data to do further analysis.

Let's move on to looking at the opinions of another important group of people: players! To begin, let's create a query very similar to the one we used in Task Four, except this one will look at user_score averages by year rather than critic_score averages.

-- Select year, an average of user_score, and a count of games released in a given year, aliased and rounded

Select year, round(avg(user_score), 2) as avg_user_score, count(game_sales.game) as num_games

From game_sales

Inner Join reviews

Using (game)

-- Include only years with more than four reviewed games; group data by year

Group by year

Having count(game_sales.game) > 4

-- Order data by avg_user_score, and limit to ten results

Order by avg_user_score desc

Limit 10;

7- Years that Both Players and Critics Loved

Alright, we've got a list of the top ten years according to both critic reviews and user reviews. Are there any years that showed up on both tables? If so, those years would certainly be excellent ones!

Recall that we have access to the top_critic_years_more_than_four_games table, which stores the results of our top critic years query.

The top_critic_years_more_than_four_games table:

We've also saved the results of our top user years query from the previous query into a table:

The top_user_years_more_than_four_games table:

-- Select the year results that appear on both tables

Select year

From top_critic_years_more_than_four_games

Intersect

Select year

From top_user_years_more_than_four_games;

8- Sales in the Best Video Game Years

Looks like we've got three years that both users and critics agreed were in the top ten! There are many other ways of measuring what the best years for video games are, but let's stick with these years for now. We know that critics and players liked these years, but what about video game makers? Were sales good? Let's find out.

This time, we haven't saved the results from the previous task in a table. Instead, we'll use the query from the previous task as a subquery in this one! This is a great skill to have, as we don't always have write permissions on the database we are querying.

-- Select year and sum of games_sold, aliased as total_games_sold; order results by total_games_sold descending

-- Filter game_sales based on whether each year is in the list returned in the previous task

Select year, sum(games_sold) as total_games_sold

From game_sales

Where year in (Select year

From top_critic_years_more_than_four_games

Intersect

Select year

From top_user_years_more_than_four_games)

Group by year

Order by total_games_sold desc;

Conclusion

In this project, I analyzed video game critic and user scores as well as sales data for the top 400 video games released since 1977. I searched for a golden age of video games by identifying release years that users and critics liked best, and I explored the business side of gaming by looking at game sales data...

Other than basic SQL queries (selecting columns, filtering rows, aggregating, sorting, and grouping), this project is showcasing my skills in joining datasets and comparing results with set theory.