Digitals_routes_players

Digitals_routes_players.sql

/*
To create a table with group_id and winner_id based on below logic 

Expected Result
group_id,winner_id
1,45
2,20
3,40

In matches tables 
 1st row : player 30 scored 10 and player 45 scored 12 , Winner = 30 (12>10)
 2nd  row : player 20 scored 5 and player 50 scored 5 , Winner = 20 (take least id)
 If player not there in matches tablee eg: 40 , he is the winner 

players table
player_id , group_id
20,2
30,1
45,1
40,3


matches tables
match_id,first_player,second_player,first_score,second_score
1,30,45,10,12
2,20,50,5,5
*/
with 
	temp as 
	    (SELECT 
	    CASE 
	        WHEN first_score > second_score THEN first_player   
	        WHEN first_score < second_score THEN second_player  
	        ELSE first_player
	    END AS winner
	FROM matches),

	temp2 as (
	select group_id ,player_id  from players 
	left outer join temp on player_id=winner
	where winner is not NULL
	order by group_id
	),

	temp3 AS (
	    SELECT group_id ,player_id FROM players
	    WHERE player_id NOT IN (
	        SELECT first_player FROM matches
	        UNION
	        SELECT second_player FROM matches
	    )
	)

select * from temp2 union select * from temp3