SQL for Data Analytics · An example analysis

An example analysis

Watch28 min

In this video I take a vague request from a streaming company and work it through from start to finish, using a table of Netflix titles. Watch for the places where I have to make a decision the request didn't make for me, and for the one check I should have done at the start.

Example Scenario28 min

A note on this video

The Netflix table in the video isn't loaded on this site, so there's no query box here. The rest of the unit uses the video game table instead.

The steps

These are the steps I take in the video.

  • Explore the data. Look at what's in the table before you try to answer anything, including the range of any dates. In the video I didn't check the date range until the very end, when I found the data stops in 2021. That should have happened here, before I picked any years.
  • Break the request down into specific questions a query can answer.
  • Write down what's ambiguous, and what you assumed. "Recent" has to become actual years.
  • Write the queries. Start with SELECT * and build up, one piece at a time.
  • Present it. Clean up a few queries with clear column names, and say what you found.
  • Name the limitations. What the data couldn't tell you, and what you'd want next.

There's one more thing I did in the video that isn't a step of its own, which is checking the results as I went. My date filter returned nothing, a NULL director showed up in the results, and an average came out different from what I expected. Each time, I stopped to make sense of it before going on. Checking your answer, later in this unit, is about how to do this.

The rest of this unit works through these steps with the games table and a real request from a game studio.

Pause and answer

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

The request asked about "recent trends." What did I decide "recent" meant? Why is that an assumption and not a fact?

How I'd answer it

I compared titles added from 2019 to 2024 with titles added from 2013 to 2018, and used the last three to five years for the other questions. Nobody told me to do that. "Recent" could mean the last few months to somebody at Netflix. It was a reasonable choice, but it was my choice, so it belongs in the list of assumptions where the person reading can see it and disagree.

When I counted titles per director, why did I change COUNT(*) to COUNT(director)?

How I'd answer it

About 2,600 titles have no director. When I grouped by director, all of those went into one group, labeled NULL, and COUNT(*) counted every row in it. So the results had a row that looked like a director with 2,600 titles.

COUNT(director) only counts rows that have a director. None of the rows in that group do, so its count becomes 0, and it sinks to the bottom when you sort.

The query ran without an error both ways. I only caught it by looking at the results.