I was working through my own scripts in Question 12 on Twitch Gaming Data, and wanted to create a table showing the total number of streams for each gaming genre, grouped by country.
Example result: In the US there were XX streams in the MOBA genre, XX streams in the FPS genre, XX streams in the Survival genre…In AU there were XX streams in the MOBA genre, XX streams in the FPS genre, etc.
I’m trying to use window functions with PARTITION BY to get the total count of streams by genre for each country in this script below. I copied the CASE statement from Question 7 in the same exercise to get the genres for each game. My issue is that when I run this script, the ‘genre_count’ always returns a value of 1 rather than the total number of streams in the genre (for that country):
SELECT country, genre, COUNT(*) OVER (PARTITION BY country) AS genre_count FROM ( SELECT game, CASE WHEN game = 'Dota 2' THEN 'MOBA' WHEN game = 'League of Legends' THEN 'MOBA' WHEN game = 'Heroes of the Storm' THEN 'MOBA' WHEN game = 'Counter-Strike: Global Offensive' THEN 'FPS' WHEN game = 'DayZ' THEN 'Survival' WHEN game = 'ARK: Survival Evolved' THEN 'Survival' ELSE 'Other' END AS 'genre', country FROM stream) WHERE country IS NOT NULL GROUP BY 1;
Am I thinking about this the right way? Is COUNT(*) OVER (PARTITION BY country) the right way to set up this window function?