Hint on some possible query improvement I am currently learning SQL on my own and I am looking for a review of one of my query.

I have the following problem.

"Given a table of players and a table of played match output the ranking of the tournament: (id, player_name, win, number_of_games)"

To that I have created to table

CREATE TABLE players (
id SERIAL PRIMARY KEY,
name char(50)
);
-- results =&gt; id of the player who won
CREATE TABLE game (
id SERIAL PRIMARY KEY,
player1 integer,
player2 integer,
results integer
);


I have come up with a query that do the job. But I find it dificult to understand.

SELECT S2.playersid as id, S2.name, COALESCE(S1.win, 0) as win, COALESCE(S2.games, 0) as games  
FROM
  (
   SELECT results as id, COUNT(1) as win
   FROM game
   GROUP BY game.results
  ) AS S1
RIGHT JOIN
  (
   SELECT players.id as playersid, SUM(games) as games, players.name as name
   FROM
   (
       SELECT game.player1 as id, COUNT(game.player1) as games FROM game GROUP BY game.player1
       UNION ALL
       SELECT game.player2 as id, COUNT(game.player2) as games FROM game GROUP BY game.player2
   ) as computeGames
   RIGHT JOIN players
   ON computeGames.id=players.id
   GROUP BY players.id, players.name
  ) AS S2
ON S1.id=S2.playersid
ORDER BY win DESC, games DESC;


Is there any features that I might have missed that can help me simplify this query?

You can test it over there http://rextester.com/EYPW43210
Thanks for the test data, makes it much easier. First reflection is that almost all columns are nullable, I wonder whether that's deliberate because it makes querying more difficult. In the sketch below I'll assume that columns are mandatory. You may also want to add the following constraints to game

CHECK( player1 &lt;&gt; player2 )            -- can't play against self
CHECK( results in (player1, player2) ) -- winner must participate 

select p.id, p.name
     , count(case when g1.results = p.id then 1 end) as win
     , count(g1.player1) as games -- any column from g1 will do  
from players p
left join game g1
   on p.id = g1.player1
   or p.id = g1.player2
group by p.id, p.name
order by win DESC, games DESC;


If you make a left join between player and game (given the join condition), you will get all players and all games where player is either player1 or player2.

To count all games we count the rows where either player1 or player2 is not null. Note that we can not count the rows (say with count(*)) since we then would count rows where the player does not participate.

To count the wins we count the rows where the winner match the player.