SQL for Data Analytics · What is one row?

What is one row?

The studio's request

For the rest of this unit, you're doing data analysis for a small game studio. They're deciding what to make next, and they've sent you this:

We're trying to decide what our next big game should be. Can you look at the sales data and tell us what sells? We'd especially like to know which genres sell best.

It sounds like one question, but it's really several. What counts as a game? Is the market for games growing, or is this a bad time to make one? Which genres sell best? Each one needs its own queries, and each one can go wrong in its own way. This lesson starts with the first one.

The data you have is a table called games. It has about 16,600 rows of sales numbers for video games, copied from a website that tracks game sales. On this page and on the practice page, the query boxes run against the games table instead of Northwind.

The games table

Run this and look at what's in the games table.

The first ten rows of the games table.
⌘/Ctrl + Enter

Each row has the game's name, the platform it came out on, the year, the genre, and the publisher. Then there are five sales columns: North America, Europe, Japan, everywhere else, and the global total. Sales are in millions of copies. The 82.74 next to Wii Sports means 82.74 million copies, not dollars.

Releases, not games

Before you count or add anything in a table, figure out what one row stands for.

Look up Grand Theft Auto V.

Every row for Grand Theft Auto V.
⌘/Ctrl + Enter

It's in the games table five times, once for each platform it came out on. So a row isn't a game. A row is one game on one platform. For the rest of this unit, I'll call that a release.

The studio wants to know what sells. The simplest version of that is the best-selling game. What does a row being a release, not a game, have to do with the answer?

How I'd answer it

If you sort the rows, you're ranking releases, so a game that sold well on five platforms has its sales split five ways. To rank games, you have to add up each game's rows first. The two rankings come out different, as the next two queries show.

The first way to rank is by sorting the rows.

Write a query that shows the five best-selling releases. Show the name, the platform, and the global sales, highest first.
⌘/Ctrl + Enter

Grand Theft Auto V isn't on it. Its best row, the PS3 version, comes 17th. To rank games instead of releases, group by the name, so all of a game's rows get added up together.

Write a query that shows the five best-selling games, with every platform added together. Show the name and the total copies, highest first.
⌘/Ctrl + Enter

Ranked as a game, Grand Theft Auto V is second of all time, behind only Wii Sports.

Neither ranking is wrong. They answer different questions. "Which single release sold the most?" and "Which game sold the most?" are both reasonable things to ask. But somebody asking "What's the best-selling game?" probably has one of them in mind, and you need to know which.

COUNT(DISTINCT)

How many games are in the games table? It sounds like COUNT(*) would tell you. Run it.

Count the rows in the games table.
⌘/Ctrl + Enter

That gives 16,598. But COUNT(*) counts rows, and a row here is one game on one platform. Grand Theft Auto V alone is five of those rows. So 16,598 is the number of releases, not the number of games.

To count each game once, put DISTINCT inside the COUNT.

Count the different names in the games table.
⌘/Ctrl + Enter

That gives 11,493. COUNT(DISTINCT column) counts how many different values are in that column. A name that shows up on five rows is counted once. Like COUNT(column), it skips NULLs.

You'll see DISTINCT on its own after SELECT, too. This lists each platform once, instead of once for every row.

Every platform in the games table, each one once.
⌘/Ctrl + Enter
Write a query that returns the number of different publishers in the games table.
⌘/Ctrl + Enter
Write a query that lists every genre in the games table, each one once.
⌘/Ctrl + Enter
Pause and answer

These are just for you. They aren't graded or turned in.

Somebody asks how many games Nintendo has published. COUNT(*) with WHERE Publisher = 'Nintendo' gives 703. COUNT(DISTINCT Name) gives 667. Which would you give them, and what would you say about the other one?

How I'd answer it

667 is the number of different games, which is what they asked about. I'd mention the 703 too: it's the number of releases, because some games came out on more than one platform. If they're really asking how much work Nintendo has put out, the releases might be what they care about.

A coworker runs SELECT AVG(Global_Sales) FROM games;, gets 0.54, and reports that "the average game sells about 540,000 copies." What's wrong with that? Would the average per game be higher or lower?

How I'd answer it

AVG averages over rows, and a row is a release, so 0.54 million is the average per release. A game that came out on five platforms has its sales split across five rows, and each of those smaller numbers gets averaged in separately. Add each game's rows up first and the average per game is higher, about 0.78 million.