Finding the MAX(COUNT(login)) per country per game TWITCH data project I

TWITCH data project I

I am struggling with WINDOWS FUNCTIONS / GROUP BY and the question I created that I want to answer: From the chat table, WHICH country chatted the most about WHICH game?

So, I want a table that has the country, the game and the chat COUNT(login) OF the country that chatted the most about THAT game…

Maybe the answer is that this question is ridiculous…

With a query that uses GROUP BY I can isolate the COUNT of login(s) for each game ONLY if I specify the country in a WHERE clause. As soon as I try to use a WINDOWS function to PARTITION BY country then the COUNT function is not aggregated by game even when using ORDER BY game.

I realize that I don’t fully understand the WINDOWS FUNCTIONS - I re-read what I learned about them and PARTITION BY will show the column for every aggregation…

EDIT: I have been experimenting and this gets me close:
SELECT COUNT(login), game, country

FROM chat

WHERE game != ‘game’ AND country != ‘country’

GROUP BY game, country


I have to use the != ‘game’ and != ‘country’ because these fields actually exist…not just as headers.

In case there’s anyone out there who reads this, I eventually figured it out with advice from the Reddit community at r/SQL and Google Search.
A side-issue here is that the chat table has some fields that cause errors as well. Or at least, I felt the need to filter them out…
Basically, create a CTE including the windows function that ranks the login count. grouped by game and country. See below:
WITH ranked AS (
ORDER BY COUNT(login) DESC) AS ‘Rank’, COUNT(login) AS ‘Logins’, game, country
FROM chat
WHERE game != ‘game’ AND country != ‘country’
GROUP BY game, country
ORDER BY game, COUNT(login) DESC)
SELECT Logins, game, country
FROM ranked
WHERE Rank = 1