Duplicate entries in query with sum
You don't need the derived table, you can use the window function to order by the sum.

The LEFT JOIN works as an INNER JOIN as columns from both tables appear in the WHERE clause, so it's probably better to explicitly state it as an inner join.

I added the school_name in the joining condition. It seems like it be there but it's not very clear in the question description. (Could a player be in one school and appear in a different school in the double table? The foreign key definition indicates that it can't.)

You state that you want "for each player, its total score over all tournaments" but the code has WHERE tournament_number &gt;= 2. Which is correct?

SELECT d.player_name_fkey,
       d.school_name_fkey,
       sum(d.score) AS sum_scores,
       rank() OVER w AS scorerank
FROM players AS p 
    JOIN double AS d
    ON  p.name = d.player_name_fkey
    AND p.school_name = d.school_name_fkey
WHERE p.gender = 'Masculin' 
  AND d.category = 'Benjamin'
  AND d.tournament_number &gt;= 2
GROUP BY d.player_name_fkey,
         d.school_name_fkey 
WINDOW w AS (PARTITION BY d.school_name_fkey ORDER BY sum(d.score) DESC) ;