SQL for Data Analytics · Checking your answer
Checking your answer
Before the studio picks what kind of game to make, it's worth knowing whether the market is growing or shrinking. How have video game sales changed over the years? This query looks like it answers that. Run it.
Read the result the way the studio would. What does it say happened to the video game industry?
How I'd answer it
Taken at face value, sales peaked in 2008 and 2009 and fell almost every year after that. In 2012 the number of releases dropped by more than 40% and sales by about 30%. By 2016, sales were about 90% below the peak. Read that way, the games table says the industry has been shrinking for years.
The games table says the video game industry has been shrinking since 2009. That isn't what happened. The video game industry kept growing through the 2010s. The query is right, so something about the data is off. Here it is again.
The most extreme drops are at the very end. 2016 has about half as many releases as 2015, and 2017 has almost none. One reasonable explanation is that the data was collected partway through 2016, so newer games never made it in. That turns out to be true. I checked, and the dataset was created in October 2016.
That explains 2016. It doesn't explain why sales fall almost every year after 2009, and nothing in the games table does.
There's something else odd about the years. Some rows in the games table are dated after 2016. Run this to see them.
The one 2020 row is Imagine: Makeup Artist, for the Nintendo DS. There are also three rows from 2017.
If the data was collected partway through 2016, how can it have rows from 2017 and 2020? What does that make you wonder about the rest of the table?
How I'd answer it
It can't be both. Either some games were in the data before they came out, or some years are wrong. The DS one is almost certainly wrong, since new DS games stopped coming out years before 2020. And once you know some of the years are wrong, you have to wonder what else is. Are the sales numbers right? Which games got included, and why? You can't answer that from the games table. You have to find out how the data was made.
You can come up with a plausible explanation for each of these on its own. The 2016 drop is a partial year. The DS game could be a typo. Maybe the site the data came from tracked fewer games after 2012. Each one sounds fine by itself. Together, they point to a bigger question, which is how this data was collected. That doesn't make the games table useless. It might still tell you a lot. But you can't take it at face value, and you can't know what it can tell you until you know how it was made, what it counts, and what it leaves out.
The next step would be a much deeper investigation into where the games table's data came from. If this were a table you'd been handed and had to draw real conclusions from, you'd almost certainly need to do that. We won't here, because it starts to become a research project, and that's beyond what this unit covers. If you're curious, the games table comes from a dataset posted on Kaggle.
The important thing to take away at this stage is noticing. These problems don't always announce themselves. The queries run and the numbers look reasonable at a glance. You have to be paying attention.
I didn't notice these problems when I first picked the games table. I found them while reviewing this unit, and I kept the table on purpose, because noticing this kind of thing is a big part of working with real data. We'll keep using it for the rest of the unit and in the practice. You can still use it to answer questions about what the data says, as long as you keep its limits in mind.
Here are sales by year one more time.
The studio asked whether the market for games is growing, and the games table is the data you have. Based on it, what would you tell them?
How I'd answer it
I'd tell them the games table can't answer that. Its sales fall almost every year after 2009, but the video game industry grew over the same years. We also don't know how the data was collected, so the drop may only be in what got recorded. Answering the question would take a different source, like industry revenue figures. If you were writing this up, it would go under the limitations.
The studio might not know any of this. The people who ask you for an analysis often don't know everything about their own data, like what gets collected and what doesn't. A reporting tool might have stopped working a year ago, or an outage might have left a gap, and nobody noticed. You may be the first person to look closely enough to find out.
The games table has 16,598 rows. You can't read them all, and a real table might have millions. So most of your checking happens on summaries, like counts, totals, and averages. The first kind of check is a sanity check. You think of something that should obviously be true, and make sure it is.
The four regional columns should add up to Global_Sales. Run this to compare them across the whole games table.
Across the whole games table, global sales come to 8,920.44 million, and the four regions add up to 8,915.85 million. They're off by 4.59 million copies out of almost 9 billion, because each number was rounded to two decimals. If the gap were hundreds of millions of copies, something would be wrong.
The rows you're relying on should have the values you need. Some rows in the games table have no year. When you filter by year, as in WHERE Year >= 2008, those rows are left out, and nothing tells you.
Use IS NULL. = NULL runs without an error but never matches anything.
That's 271 releases, about 100 million copies, left out of anything that filters on year. That may be fine, but you should know it's happening.
When a summary looks off, or you want to be sure it's doing what you think, go down a level. Look at the rows behind it, or break it down a different way, and check that. Drilling down like this is a big part of the job.
The simplest version is a spot check. Pick something you know about and look at its rows. If you know Grand Theft Auto V came out on five platforms, and your query says it's on two, the query has a problem, and it probably has the same problem for every other game too.
Another is looking at what's behind an average. Say the studio is thinking about making a shooter. Run this to see the genres in the games table with the highest average sales.
Shooters come second. The average shooter release sold about 0.79 million copies, behind only Platform games. That sounds like good news for the studio. Before you tell them so, drill down to the releases behind the average.
Look at the ten best-selling shooter releases. What's pulling the average up?
How I'd answer it
A handful of huge hits. The top one, Duck Hunt, came packaged with the NES in some bundles, which is a big part of how it reached 28 million. Eight of the next nine are Call of Duty games, all from one publisher. Releases that big pull the average far above what a typical shooter release sells.
So how many shooter releases actually reach that average?
That's 304 of the 1,310 shooter releases, about a quarter, and 389 of them sold under 100,000 copies. For a small studio, "shooters average 0.79 million copies" is misleading. Most shooter releases sell far less, and the ones that sell the most come from a few very large publishers.
Each time you drill down, you end up with something new to check, like a smaller summary or a handful of rows. You go back and forth like that until the numbers make sense, or until you find out why they don't.
These are just for you. They aren't graded or turned in.
Platform games have the highest average sales per release in the games table. Before you tell the studio to make a platformer, what would you check?
How I'd answer it
Drill down to the releases behind the average. The eight best-selling platform releases are all Super Mario games, all from Nintendo. Nintendo has 112 of the 886 platform releases but about half of the genre's sales, and only 203 of the 886 reach the average. The average says more about Mario than about the kind of platformer a small studio would make.
The studio asks you for the average sales of each genre in the games table. What about the data would you keep in mind while you answer? Which of those would you actually tell the studio?
How I'd answer it
There are several things you might mention.
- The data is old. It ends partway through 2016, so it describes past sales. A genre that sold well in 2008 might sell very differently today.
- Sales are counted in copies. The table says nothing about price, revenue, or what a game cost to make, so it can't tell you which genres make money.
- You don't know exactly how the numbers were collected. They come from one website, you don't know the details of how it got them, and some of the rows are odd, like the ones dated after 2016.
- A few huge hits pull averages up. A genre's average can be far above what a typical release in that genre sells.
- The averages are per release. A game that came out on five platforms counts five times.
You wouldn't read all of these to the studio. Tell them the ones that would change what they decide, in a sentence next to the number they affect. That a few huge hits pull a genre's average up matters directly to a small studio picking a genre, so you'd say it. That the averages are per release belongs in the column name, like "average copies per release," so the studio doesn't need a separate warning about it.