Query to get the best scorer per game

Question

I need to calculate the best scorer(player_id / player_name) per game_id. As you can see below, I have three cases:

• Two or more guys have the same number of goals: in that case, we do not have any scorer.
• One guy is the only goal scorer.
• We have one scorer with clearly the highest score.

I have tried to make a query to give me the game_id, player_name, player_id of the best scorer per game but without success. Here is my query:

select j.id as game_id,jg.id as player_id, jg.nome s player_name,
       count(g.id) as numberOfGoals,
       RANK() OVER(PARTITION BY j.id  ORDER BY count(g.id) ) as rank 
from jogo j
inner join jogo_jogador jj on jj.jogo_id = j.id
inner join golo g on g.jogo_jogador_id = jj.id
inner join equipa_jogador ej on ej.id = jj.equipa_jogador_id
inner join jogador jg on jg.id = ej.jogador_id
group by jg.id, jg.nome, j.id
order by j.id, jg.nome, jg.id;

This is what I currently get:

insert into tbl(game_id player_id   player_name numberofgoals   rank) 
values 
(1 , 1 ,'Marco Costa'    ,1  ,1), 
(1 , 4 ,'Olivier Marques',1  ,1), 
(2 , 1 ,'Marco Costa'    ,1  ,1), 
(3 , 9 ,'Ilidio Vatuva'  ,2  ,2), 
(3 ,10 ,'Joaquim Barros' ,1  ,1),
(4 ,11 ,'Diogo Mendonça' ,2  ,4), 
(4 ,10 ,'Joaquim Barros' ,1  ,1), 
(4 ,14 ,'John Smith'     ,1  ,1), 
(4 ,12 ,'Mário Jorge'    ,1  ,1), 
(5 , 7 ,'Ricardo Pereira',1  ,1), 
(6 , 8 ,'Danilo Barbosa' ,1  ,1), 
(6 , 9 ,'Ilidio Vatuva'  ,1  ,1), 
(6 ,19 ,'Micael Pereira' ,1  ,1), 
(6 ,18 ,'Ricardo Bateiro',2  ,4),
(7 , 8 ,'Danilo Barbosa' ,3  ,1), 
(9 , 8 ,'Danilo Barbosa' ,1  ,1),
(9 , 2 ,'Joao Azevedo'   ,1  ,1),
(9 , 7 ,'Ricardo Pereira',1  ,1), 
(10, 9 ,'Ilidio Vatuva'  ,1  ,1), 
(11, 3 ,'Kevin Soares'   ,1  ,1), 
(11, 1 ,'Marco Costa'    ,1  ,1),
(11,18 ,'Ricardo Bateiro',2  ,3), 
(12,21 ,'Daniel Silva'   ,1  ,1), 
(12, 9 ,'Ilidio Vatuva'  ,1  ,1), 
(13, 2 ,'Joao Azevedo'   ,1  ,1);

I am working with PostgreSQL 13.2.

For game_id 1 (as example):

• If I run the subquery without limit I get two possible winners, with the same number of goals (1). In that case, we don't have a scorer. We don't choose anyone.
• If I run the subquery with Limit, it is chosen one player.

Answer 1

the best scorer(player_id/player_name) per game_id.

Going out on a limb, this might do it:

SELECT j.id AS game_id
     , x.player_id
     , jg.nome AS player_name
     , x.number_of_goals
FROM   jogo j
JOIN   LATERAL (
   SELECT jj.id AS player_id 
        , count(*) AS number_of_goals
        , lag(count(*)) OVER (ORDER BY count(*) DESC) AS next_best  -- descending!
   FROM   jogo_jogador   jj
   JOIN   golo            g ON g.jogo_jogador_id = jj.id
   WHERE  jj.jogo_id = j.id
   GROUP  BY jj.id
   ORDER  BY count(*) DESC, next_best DESC NULLS LAST
   LIMIT  1
   ) x ON x.number_of_goals > x.next_best  -- better than the next best
       OR x.next_best IS NULL              -- or there was no next best
JOIN   jogador jg ON jg.id = x.player_id;

In the LATERAL subquery x I count goals per player, order players in descending (DESC !) order and pick the one with the highest score - if it's better than the next best (or there is no next best).

next_best is determined using the window function lag(), based on the same descending order.

About DESC NULLS LAST:

• Sort NULL values to the end of a table

Only retrieve the player name for actual winners after that.

Without knowing your relational design, I assume jogo_jogador.id is actually the player ID (jogador.id) and there is no need to join to equipa_jogador at all.

Using the slightly faster count(*) (instead of count(g.id)) because we can.