Get the highest category while performing other type of aggregates

Story:

I am trying to get the sum of records and the max date by country and games and another column that would rank the top countries based on the sum of records:

select id, country, game, sum(records) as records, max(date) as max_date
from table
group by id, country, game

It is the country rank column that is giving me trouble. Here’s what I tried:

ROW_NUMBER() OVER(PARTITION BY id, country ORDER BY SUM(records) DESC) as rn

All it does is rank the each row partition by country which is what I expected.

Objective

Is there a way to achieve what i want in one or two subqueries?

Here’s a desired output

+----+---------+--------------+---------+------------+------+
| id | country |     game     | records |  max_date  | rank |
+----+---------+--------------+---------+------------+------+
|  2 | usa     | wow          |      10 | 2019-01-01 |    1 |
|  2 | usa     | wakfu        |      15 | 2019-01-01 |    1 |
|  2 | usa     | clash royale |      30 | 2019-01-01 |    1 |
|  2 | germany | dofus        |       9 | 2019-01-01 |    2 |
+----+---------+--------------+---------+------------+------+

Here for ID #2, country USA is 1st due to its combined sum of records from all games.

To the request of comments below:

Raw data looks like that:

+----+---------+--------------+---------+------------+--+
| id | country |     game     | records |  max_date  |  |
+----+---------+--------------+---------+------------+--+
|  2 | usa     | wow          |       2 | 2018-12-01 |  |
|  2 | usa     | wow          |       5 | 2018-12-05 |  |
|  2 | usa     | wow          |       1 | 2018-12-10 |  |
|  2 | usa     | wow          |       2 | 2019-01-01 |  |
|  2 | usa     | wakfu        |      10 | 2018-12-10 |  |
|  2 | usa     | wakfu        |       5 | 2019-01-01 |  |
|  2 | usa     | clash royale |      30 | 2019-01-01 |  |
|  2 | germany | dofus        |       2 | 2018-05-01 |  |
|  2 | germany | dofus        |       4 | 2018-07-01 |  |
|  2 | germany | dofus        |       3 | 2019-01-01 |  |
+----+---------+--------------+---------+------------+--+

Answer

You can build on your aggregation query. This version produces a ranking that is like row_number(), so ties would get different values:

select id, country, game, records, max_date,
       dense_rank() over (order by country_sum desc, country) as ranking
from (select id, country, game, sum(records) as records, max(date) as max_date,
             sum(sum(records)) over (partition by country) as country_sum
      from mytable
      group by id, country, game
     ) cg;

Here is a db<>fiddle.