SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   id      INTEGER  NOT NULL 
  ,country VARCHAR(9) NOT NULL
  ,game    VARCHAR(14) NOT NULL
  ,records INTEGER  NOT NULL
  ,date    DATE  NOT NULL
);
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'usa','wow',2,'2018-12-01');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'usa','wow',5,'2018-12-05');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'usa','wow',1,'2018-12-10');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'usa','wow',2,'2019-01-01');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'usa','wakfu',10,'2018-12-10');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'usa','wakfu',5,'2019-01-01');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'usa','clash royale',30,'2019-01-01');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'germany','dofus',2,'2018-05-01');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'germany','dofus',4,'2018-07-01');
INSERT INTO mytable(id,country,game,records,date) VALUES (2,'germany','dofus',3,'2019-01-01');
10 rows affected

select 
    id, 
    country,
    game,
    game_records records,
    date max_date,
    dense_rank() over(order by country_records desc) rnk
from (
    select 
        t.*,
        rank() over(partition by id, country, game order by date desc) rn,
        sum(records) over(partition by id, country, game) as game_records,
        sum(records) over(partition by country) country_records
    from mytable t
) t
where rn = 1
order by rnk, records


id
country
game
records
max_date
rnk


2
usa
wow
10
01/01/2019 00:00:00
1

2
usa
wakfu
15
01/01/2019 00:00:00
1

2
usa
clash royale
30
01/01/2019 00:00:00
1

2
germany
dofus
9
01/01/2019 00:00:00
2


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


id
country
game
records
max_date
ranking


2
usa
clash royale
30
01/01/2019 00:00:00
1

2
usa
wakfu
15
01/01/2019 00:00:00
1

2
usa
wow
10
01/01/2019 00:00:00
1

2
germany
dofus
9
01/01/2019 00:00:00
2

id	country	game	records	max_date	rnk
2	usa	wow	10	01/01/2019 00:00:00	1
2	usa	wakfu	15	01/01/2019 00:00:00	1
2	usa	clash royale	30	01/01/2019 00:00:00	1
2	germany	dofus	9	01/01/2019 00:00:00	2