-- add the group for the matches
-- first_player, sum(score) group by first_player , group_id
-- second_player, sum(score) group by second_plauer , group_id
-- union all - group by player, group_id
-- dense_rank () over ( partition by group_id, order by score desc , player_id )

with firstPlayer as (

select p.group_id , m.first_player as player , sum(first_score) as score from 
matches m 
join 
players p 
on m.first_player = p.player_id
group by
group_id , m.first_player

),

secondPlayer as (
select group_id , m.second_player , sum(second_score ) as score from
matches m
join
players p
on m.second_player = p.player_id
group by
group_id , m.second_player

),

unionPlayer as(

select group_id, player, score from firstPlayer
union all
select group_id, second_player, score from secondPlayer

),

totalScore as (

select group_id, player, sum(score)
from unionPlayer 
group by group_id, player

),

rank as (

select group_id , player_id , dense_rank() over (partition by group_id order by score desc, player_id asc) as rnk

)

select group_id , player_id from rank where rnk = 1