Require a better SQL Query to find the winner of a match

Question

Hi All,

I have given a SQL exam a couple of days ago. I wanted to see if the solutions to one of the problems could have been any better.

I'm recollecting the question from memory and this is how the question was.

There are 2 tables namely "Players" and "Matches". I needed to find the player who scored maximum points per group. Let me show you the data and explain to you a few more conditions.

Players Table (All the fields are Integers)

Player_ID Group_ID
45 1
30 1
65 1
25 1
50 2
20 2
40 3

Matches (All the fields are Integers)

A few points.

• Player_ID field in Players table is the Player's ID. This field can be joined to the first_Player or Second_Player fields of Matches tables.
• Match_ID is the Primary Key of Matches table and Player_ID is the Primary Key of Players table.
• One key thing to note is, a match is only played between same group players (example: Player 50 will never play a match with Player 45 because they both belong to different groups. Player 50 plays only with other player in the same group which in this case is 20)
• Going by the above logic Player 40 will never be able to play any match as there isn't any other player in his group

Now the Requirement is:

• Get the player from each group who has scored maximum points (among all the different matches they played)
• If the scores are tied, select the lowest ID player as the winner
• If there is only one player in any group, they will automatically become the winner.
• To picturise the answer should be.

Group_ID Winner_ID

1 45

2 20

3 40

Explanation of the result set

• If we see group 1, player 45 scored 12 points in match_id=1 and scored 10 points in match_id=13, scored 8 points in match_id =42 and scored 4 points in match_id=52. His total score is 34. No other player in group 1 has scored more than 34. so he is the overall winner for group 1
• If we see group 2, there was only one match between player 20 and 50 and they both scored 5 points each. When there is a tie like this, the winner is with lowes Player_ID. In this case, the winner is Player_ID=20
• Now there is only one player from Group 3, and that player automatically becomes winner without playing any game.

For this, during the exam, I wrote a very complex query by unpivoting the data(although I got the proper result) but when I got out of the exam I thought about the exam and I wrote a better query (which I still think can be polished).

;WITH cte AS   
(  
	SELECT   Group_ID  
			,Player_ID  
	        ,SUM(Score) AS Score  
			,ROW_NUmber() OVER (Partition BY group_id Order by suM(score) desc,player_id) AS RNM   
	FROM (  
			SELECT   P.group_id  
					,P.Player_ID  
					,COALESCE(M.first_score,0) AS Score   
			  
			FROM 		matches M  
			RIGHT JOIN 	Players P  
					ON 	M.first_player = P.Player_id  
			  
			UNION ALL  
  
			SELECT 	 P.group_id  
					,P.Player_ID  
					,COALESCE(M.Second_score,0) AS Score   
		      
			FROM 		matches M  
			RIGHT JOIN 	Players P  
					ON 	M.Second_player = P.Player_id  
		)A  
GROUP BY GROUP_ID, PLAYER_ID  
)  
  
SELECT GROUP_iD, pLAYER_id AS Winner_ID FROM CTE  
WHERE rnm=1  

Now my question:

Is there a better way of writing the above query? Can we simplify it even further? I am just not liking those many subqueries.

Any better solution will be appreciated.

Regards,

MJ

Answer 1

Hi @Roger Binny ,

Welcome to the Microsoft TSQL Forum!

From your query alone, it seems that it cannot be simplified. If you don't like nested subqueries that are not easy to read, you can replace all subqueries with cte. Of course, this is actually the same thing, but cte is easy to read.

Please refer to:

;WITH cte1 as   
        (SELECT P.group_id,P.Player_ID,COALESCE(M.first_score,0) AS Score FROM matches M  
        RIGHT JOIN Players P ON M.first_player = P.Player_id  
        UNION ALL  
        SELECT P.group_id,P.Player_ID,COALESCE(M.Second_score,0) AS Score FROM matches M  
        RIGHT JOIN Players P ON M.Second_player = P.Player_id)  
,cte2 as  
      (SELECT Group_ID,Player_ID,SUM(Score) AS Score,ROW_NUmber() OVER (Partition BY group_id Order by suM(score) desc,player_id) AS RNM   
       FROM cte1  
	   GROUP BY GROUP_ID, PLAYER_ID)  
  
SELECT GROUP_iD,Player_ID AS Winner_ID FROM cte2  
WHERE rnm=1  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.