create table players
(player_id int,
group_id int)

insert into players values (15,1);
insert into players values (25,1);
insert into players values (30,1);
insert into players values (45,1);
insert into players values (10,2);
insert into players values (35,2);
insert into players values (50,2);
insert into players values (20,3);
insert into players values (40,3);

create table matches
(
match_id int,
first_player int,
second_player int,
first_score int,
second_score int)

insert into matches values (1,15,45,3,0);
insert into matches values (2,30,25,1,2);
insert into matches values (3,30,15,2,0);
insert into matches values (4,40,20,5,2);
insert into matches values (5,35,50,1,1);

select * from players;
select * from matches;


-- Write an SQL query to find the winner in each group
--The winner in each group is the player who scored the maximum total points within the group.
--In the case of a tie, -- the lowest player_id wins.

-- Taking the data from first_player
WITH player_tbl as (
SELECT first_player as player,SUM(first_score) as score
FROM matches
GROUP BY first_player
UNION ALL
-- Taking the data from second_player
SELECT second_player as player,SUM(second_score) as score
FROM matches
GROUP BY second_player
)
--grouping by
, player_tbl2 as(
SELECT  player as player,
SUM(score) as score
FROM player_tbl
GROUP BY player
)
-- combine the tables with player table
,comb_tbl as (
SELECT * 
FROM players A
JOIN player_tbl2 B ON A.player_id = B.player
)

-- seperate the rows by ROW_NUM and take the highest one
,final_tbl as(
SELECT
ROW_NUMBER() OVER (PARTITION BY group_id
ORDER BY group_id,score DESC,player_id) as ROW_NUMBER,
player_id as player_id,
group_id as group_id,
score as score
FROM comb_tbl
)

SELECT group_id as group_id,
player_id as player_id,
score as score
from final_tbl
WHERE ROW_NUMBER=1
by