SQL Exam Hard Question

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 a group. Let me show you the data and explain 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)

match_id first_player Second_Player first_score second_score
1 30 45 10 12
2 20 50 5 5
13 65 45 10 10
5 30 65 3 15
42 45 65 8 4
52 45 25 4 8

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 Requrement 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 winner.
To picturise the answer should be.

Group_ID Winner_ID

1 45

2 20

3 40

Explanation to the result set

If we see the 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 simplyfy it even further. I am just not liking those many sub queries.

Any better solution will be appreciated.

Regards,

RB

Answer 1

Hi @Roger Binny ,

Welcome to Microsoft Q&A!

Your query is already a good solution based on your requrement with a good performance.

You could consider to create some indexes to improve the performance, too.

I tried with below query with several ctes and you could check whether it is a little helpful to you.

;with cte as (  
SELECT P.group_id  
,P.Player_ID  
,isnull(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,isnull(M.Second_score,0) AS Score   
FROM  matches M RIGHT JOIN     Players P ON M.Second_player = P.Player_id)  
,cte1 as (  
select distinct Group_ID,Player_ID,SUM(Score) over (partition by Group_ID,Player_ID order by Group_ID) AS Score from cte)  
select a.Group_ID,min(a.Player_ID) Winner_ID from cte1 a   
inner join   
(select Group_ID,max(score) over (partition by Group_ID order by Group_ID)score from cte1) b  
on a.Group_ID=b.Group_ID and a.Score=b.score  
group by a.Group_ID  

Best regards
Melissa

---

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.

Answer 2

Hi @Roger Binny ,

Please also refer below query and help check whether it is helpful to you.

SELECT group_id, player_id winner_id  
FROM  
(  
    SELECT P.player_id, p.group_id, sq2.score,   
    RANK() OVER (PARTITION BY p.group_id ORDER BY score DESC,sq2.player_id ) as position  
    FROM  
    (  
      SELECT player_id, SUM(score) score  
      FROM   
	  (  
        SELECT first_player as player_id, first_score as score FROM matches  
        UNION ALL  
        SELECT second_player as player_id, second_score as score FROM matches  
      ) as sq1  
      GROUP BY player_id  
    ) as sq2  
    right join players p  
    on p.player_id = sq2.player_id  
) as sq3  
WHERE position = 1   
order by group_id, player_id  

Best regards
Melissa

---

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.

Answer 3

WITH first_players AS (
    SELECT group_id,player_id,SUM(first_score) AS scores FROM players p LEFT JOIN matches m ON p.player_id=m.first_player GROUP BY group_id,player_id
    ),
    second_players AS (
    SELECT group_id,player_id,SUM(second_score) AS scores FROM players p LEFT JOIN matches m ON p.player_id=m.second_player GROUP BY group_id,player_id
    ),
    all_players AS (
        WITH al AS (
            SELECT group_id, player_id, scores FROM first_players 
            UNION ALL
            SELECT group_id, player_id, scores FROM second_players
        )
        SELECT group_id, player_id,COALESCE(SUM(scores),0) AS scores FROM al GROUP BY group_id, player_id 
    ),
    players_rank AS (
        SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY scores DESC, player_id ASC) AS score_rank,
        ROW_NUMBER() OVER(PARTITION BY scores ORDER BY player_id ASC) AS id_rank FROM all_players ORDER BY group_id
    )
    SELECT group_id, player_id AS winner_id FROM players_rank WHERE score_rank=1 AND id_rank=1