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