Finding winner of each player group in a tournament - PostgreSQL

Question

This question on PostgreSQL is bugging me for quite some time now. Iv'e tried myself and searched everywhere I could but could not end up with a result that takes the player from group 3 into account. So even though this question might be a duplicate, the right answer wasn't found. Would love some help.

the question is as follows: Write an SQL query that returns a table containing the winner in each group. Each record should contain the ID of the group and the ID of the winner in this group (players from the same group compete). Records should be ordered by increasing ID number of the group, and in case of a tie, the player with the lowest ID wins.

Given this schema:

players:

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+

matches:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+

For the following example:

Players table:

+-----------+------------+
| player_id | group_id   |
+-----------+------------+
| 20        | 2          |
| 30        | 1          |
| 40        | 3          |
| 45        | 1          |
| 50        | 2          |
| 40        | 1          |
+-----------+------------+

Matches table:

+------------+--------------+---------------+-------------+--------------+
| match_id   | first_player | second_player | first_score | second_score |
+------------+--------------+---------------+-------------+--------------+
| 1          | 30           | 45            | 10          | 12           |
| 2          | 20           | 50            | 5           | 5            |
| 3          | 65           | 45            | 10          | 10           |
| 4          | 30           | 65            | 3           | 15           |
| 5          | 45           | 65            | 8           | 4            |
+------------+--------------+---------------+-------------+--------------+

your query should return:

+-----------+------------+------------+
| group_id  | winner_id  | tot_score  |
+-----------+------------+------------+ 
| 1         | 45         | 30         |
| 2         | 20         | 5          |
| 3         | 40         | 0          |
+-----------+------------+------------+

in group 1 player 45 scored the most points. in group 2 both players scored 5 points, but player 20 has lower ID therefore he's the winner. in group 3 there is only one player and although he didn't play any matches, he is the winner.

the best iv'e managed to do so far is (on PostgreSQL):

SELECT group_id, player_id, score
FROM
(
    SELECT sq2.player_id, p.group_id, sq2.score, 
    RANK() OVER (PARTITION BY p.group_id ORDER BY score DESC) 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

Which outputs this:

+-----------+-----------------------+------------+
| group_id  | player_id             | score      |
+-----------+-----------------------+------------+ 
| 1         | 45                    | 30         |
| 2         | 20                    | 5          |
| 2         | 50                    | 5          |
| 3         | [NULL](instead of 40) | [NULL] (should be 0)|
+-----------+-----------------------+------------+

Could you help generate a query with the complete correct result? (with the details for the player with group 3)

Would also like to know why is the query returning NULL in player_id for the right join. Appreciate the help!

* this question apparently is also a question on Leetcode.com, called "Tournament winners" *

Answer 1

You can use distinct on and aggregation as follows:

select distinct on (p.group_id)
    p.group_id,
    player_id winner_id, 
    coalesce(sum(first_score) filter(where p.player_id = m.first_player), 0)
        + coalesce(sum(second_score) filter(where p.player_id = m.second_player), 0) 
        tot_score
from players p
left join matches m on p.player_id in (m.first_player, m.second_player)
group by p.group_id, p.player_id
order by p.group_id, tot_score desc, p.player_id

This works by joining the players and the matches with an in condition. Then, we use conditional aggregation to compute the total score of each player. Finally, distinct on gets the top player per group.

Demo on DB Fiddle:

group_id | winner_id | tot_score
-------: | --------: | --------:
       1 |        45 |        30
       2 |        20 |         5
       3 |        40 |         0

NB: Thanks for the well-asked question! SO could use more of that.

Answer 2

I am going to recommend a lateral join along with the distinct on:

select distinct on (p.group_id) p.group_id, p.player_id, sum(v.win_score)
from players p left join
     (matches m left join lateral
      (values ((case when m.first_score > m.second_score then m.first_player else m.second_player end),
               (case when m.first_score > m.second_score then m.first_score else m.second_score end)
              )
      ) v(winner, win_score)
      on 1=1
     )
     on v.winner = p.player_id
group by p.group_id, p.player_id
order by p.group_id, sum(v.win_score) desc nulls last, p.player_id;

Here is a db<>fiddle

Answer 3

here's my solution. Basically what I did:

1. make the table into two columns
2. left join, use coalesce to fix the null values
3. create score_rank, ranking based on the score (descending)
4. use where function where score_rank = 1 (this will filter players based on their highest score first)
5. now to solve the tie score of group_id number 2 (where the score is a tie and winner is based on the winner_id), create a row_number based on winner_id, ascending. Because 20 is higher than 50, it will get rank 1. now we can use where winner_rank = 1

the catch is, I think my code is not too effective because I use like 5 ctes. Looking forward to know if there's a simpler solution (I am not a fan of using subquery)

here's the dbfiddle link https://www.db-fiddle.com/f/oyTAQZL5bcFARvvjv2ad4X/1

with matches as(
select *
  from
(
select first_player as winner_id,first_score as score
from matches 
union
select second_player, second_score
from matches) as matches
),


players_1 as( 

select group_id,
coalesce(winner_id,player_id) as winner_id,
coalesce(score,0) as score
from players
left join matches on matches.winner_id = players.player_id
),


players_2 as (
select 
group_id,
winner_id, 
sum(score) as score
from players_1
group by group_id,winner_id
  ),

players_3 as (
select 
group_id, 
winner_id, 
score,
rank () over (partition by group_id order by score desc) as score_rank
from players_2
),

players_4 as (
select group_id,winner_id,score,
row_number () over (partition by group_id order by winner_id asc) as winner_rank
from players_3 
where score_rank =1
)

select group_id, winner_id, score
from players_4 
where winner_rank = 1

Answer 4

here is the solution for MSSQL.

WITH match_winners AS 
(
select case
        when m1.first_score > m1.second_score THEN m1.first_player
        when m1.first_score < m1.second_score THEN m1.second_player
        when m1.first_score = m1.second_score AND m1.first_player > m1.second_player THEN m1.second_player 
        when m1.first_score = m1.second_score AND m1.first_player < m1.second_player THEN m1.first_player
    END as 'winner_id', 
    case WHEN m1.first_score>m1.second_score THEN m1.first_score
        ELSE m1.second_score 
    END AS 'winscore',
    match_id
from matches m1
  )
SELECT  group_id, player_id, COALESCE(winscore,0)
FROM 
(
    SELECT  p.group_id,  p.player_id, SUM(mw.winscore) as 'winscore',
            rank() OVER (PARTITION BY p.group_id ORDER BY SUM(mw.winscore) DESC) AS result
    FROM players  p 
        LEFT JOIN match_winners mw ON mw.winner_id = p.player_id 
        LEFT JOIN matches m ON m.match_id = mw.match_id 
    GROUP BY p.group_id,  p.player_id
 ) rank_group_winners
 WHERE rank_group_winners.result = 1

May not be the most eloquent solution but I found it more intuitive to construct the winners table first. Obviously, open for a challenge.