Extend a single week's win/loss ratio calculation to a whole season
Assuming a standard many-to-many implementation ...

SELECT week_id
     , count(*) FILTER (WHERE week_points &gt; team_points) AS losses
     , count(*) FILTER (WHERE week_points &lt; team_points) AS wins
     , count(*) FILTER (WHERE week_points = team_points) AS draws
--     , count(*) AS total -- redundant check
FROM (
   SELECT week_id, team_id, sum(points) AS week_points
        , first_value(sum(points)) OVER (PARTITION BY week_id
                                         ORDER BY team_id &lt;&gt; 7) AS team_points
   FROM  (SELECT id AS week_id FROM weeks WHERE year = ?) w  -- your year
   CROSS  JOIN unnest(?::int[]) t(team_id)  -- your teams, see below!
   JOIN   player_scores p USING (week_id, team_id)
   WHERE  EXISTS (  -- only weeks where the team actually played
      SELECT 1 FROM player_scores
      WHERE week_id = p.week_id
      AND   team_id = ?  -- your team_id
      )
   GROUP  BY 1, 2
   ) sub
WHERE  team_id &lt;&gt; ?  -- your team_id again
GROUP  BY 1;


Returns something like:

week_id | losses | wins | draws
--------+--------+------+------
1       | 3      | 0    | 0
2       | 5      | 7    | 0
3       | 2      | 2    | 1


The key feature is the window function first_value() to get the weekly score for the team in the same query. Aggregate functions can be nested in window functions. Consider the sequence of events:


Best way to get result count before LIMIT was applied in PHP/PostgreSQL


The aggregate FILTER clause requires Postgres 9.4+. There are other ways. Details in this closely related answer on SO:


How can I simplify this game statistics query?


Assuming you provide your list of teams as array literal in the form '{1,2,3}'. There are other ways ...