Gaming Data with Twitch - Additional Question

Hi there :slight_smile:

I was working through this exercise in the Analyze Data with SQL skill path.
Step 5 asked me to:

Create a list of countries and their number of LoL viewers using WHERE and GROUP BY .

I had no problem doing that, however, I wanted to take things a little bit further and show a list of the number of viewers of each game, per country. Something like, in the US there are 400 LoL viewers and 200 Agar.io viewers, while in Mexico there are 200 LoL viewers and 50 Agar.io viewers, etc.

And…I feel like I’m having a breakdown. I tried to solve a similar problem before, and couldn’t find a way to solve it. Now here I am again, and still don’t have a clue of how to achieve what I want. And I feel like it’s such an easy thing. It’s a terrible feeling, like, if you gave me the solution I’d probably understand it, but when I try to find a way to solve it, my brain goes completely blank.
I didn’t want to ask for help because finding the solution myself is something that would’ve made me really happy.
But I’ve been stuck with this problem literally for hours…so… :disappointed:

I would really appreciate if someone could help me find a way to achieve my desired result :pleading_face:

Hello,

I definitely understand that feeling. Instead of giving you the answer, I’ll try to offer some simple reminders so that you can go back to the drawing board and discover how to do it yourself to get that satisfaction.

Just like with ORDER BY, you’re allowed to have multiple columns in your GROUP BY as well.

5 Likes

Not entirely related, but after you follow @selectall’s hint, you may want to filter out countries and games with a value of '' if this dataset still has them.

It bothered me seeing the country of Blank, so I figure it may bother others as well :joy:

4 Likes

Thank you so so much. It worked using a simple GROUP BY!! I CAN’T BELIEVE IT!! :star_struck: :sob:
I was trying to get all complicated using Window Functions for some reason :expressionless:
Here’s my final working query:

SELECT country, game, COUNT(*)
FROM stream
WHERE country IS NOT NULL AND game IS NOT NULL
GROUP BY country, game;

Now, to figure out how to achieve something else I challenged myself to do: Show the number of viewers each game/country has, even if that number is zero. (Because the current query only shows results for game/country combinations that do have viewers).
I mean, it might not be an extremely useful thing to find, but I still want to do it :sweat_smile:

This time I’ll remember to not get all complicated when it’s not even necessary.

5 Likes

Yeah, it actually is kinda annoying :laughing:
Thanks for the suggestion, I’ll make sure to implement it :smiley:

3 Likes

I don’t have PRO, but one approach might be to do two separate queries, with each one doing one game, one country-but this doesn’t seem like what you’re after. Have you tried using with and temporary tables? (I’m not sure how much help I’ve been here…)

2 Likes

Yeah, I’d like to achieve my result using one single query :nerd_face:
I did try using WITH for my first problem, but it didn’t work. I think it will be useful for solving my new problem though.

1 Like

One way you could do that is create a combination of every country and every game (think CROSS JOIN) - possibly using WITH just to keep it clean and readable.

Then your query would select the country and game from the cross-joined table mentioned above, along with the count from the table you LEFT JOIN on the game and country.

I’m not sure how much detail you’d like. What I wrote might be enough of a nudge in the direction of at least one solution. Let me know if you’d like me to elaborate more.

1 Like

Thank you so so much for your help. I actually had a very similar idea for solving it, but got all tangled up trying to use Window Functions…I can’t believe I forgot I could have more than one column in my GROUP BY.

And so here it is! The finished working query!! :star_struck:
(it outputs a veeeeery long list lol :sweat_smile:)

WITH countries AS (
  SELECT DISTINCT(country) as 'country'
  FROM stream
  WHERE country IS NOT NULL
), games AS (
  SELECT DISTINCT(game) as 'game'
  FROM stream
  WHERE game IS NOT NULL
), countries_games AS (
  SELECT *
  FROM countries
  CROSS JOIN games
)
SELECT countries_games.country, countries_games.game, COUNT(stream.country)
FROM countries_games
LEFT JOIN stream
ON countries_games.country = stream.country AND countries_games.game = stream.game
GROUP BY 1, 2;

On the countries temporary table I have all the different countries contained in the stream table, and something similar goes for the games temporary table. Then I performed a CROSS JOIN between those 2 tables, and ended up with a list of all the possible combinations between countries and games. I then LEFT JOINed my countries_games table (such a creative name, isn’t it) with the stream table, allowing me to get all the corresponding rows for each country-game combination. Finally, I used a simple COUNT and GROUP BY to get to the final result. I grouped by country and game.

Getting to solve this problem taught me a very important lesson: don’t try to get all complicated if you don’t even know why you’re doing it.
Break down the problem into logical steps, then turn those steps into code. And try to do it as simply as possible.

Also, asking for help doesn’t make you “dumb”. Of course, you should give the problem a try yourself, before saying it’s too complicated and asking for the solution right away.

Thanks again for your help!! :smiley_cat:

3 Likes