I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project.

The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be something missing please point it out):

basic schema

It's a database of a fantasy sports league, and we want to calculate an 'all-play record'.

My understanding is that the 'all-play record' is generated by taking every team in a given league's scores for a given week and assuming they all play each other.

I've written a query that does this for a given week:

WITH weekly_points_table (team_id, max_points)
  SELECT teams.id, SUM(player_scores.points) as max_points
  FROM player_scores
  INNER JOIN weeks ON weeks.id = player_scores.week_id
  INNER JOIN teams ON teams.id = player_scores.team_id
  WHERE (team_id IN (?) AND weeks.number = ? and weeks.year = ?)
  GROUP BY teams.id
FROM weekly_points_table
WHERE max_points < (SELECT max_points
  FROM weekly_points_table
  WHERE team_id = ?

The list of team_ids, the given week number/year, and the final team_id are passed in.

Wins are calculated with the above query, losses are calculated by simply flipping the sign, and ties are just (count of all teams - 1) - (wins + losses) for a given week.

But now I'm at a loss at how to extend this to a given season.

A season is a collection of weeks where the year is identical, and week numbers go from 1 to 16.

Right now we're literally just taking the single-week query and running it in a for-loop. (Before that, we were using nested for loops to calculate the wins/losses/ties, and you can imagine how slow that can be.)

Pretty much, how do I take the calculated wins/losses for a given week and add them up for all 16 weeks?

  • I've found a solution to my problem, and made a gist that was slightly too long to post as a comment: gist.github.com/shadefinale/fa1543ddcd0e423986f8 It uses the information from the CTE and counts rows where the weekly points are higher than the target team's weekly points. – shadefinale Aug 18 '15 at 5:55
  • Yes, some things are missing: Postgres version, data types and PK / UNIQUE constraints - you can get everything with \d tbl in psql. Exact form of parameter values. Some sample values and a corresponding desired result to clarify would be great, too. And it's ok (and actually welcome) to answer your own question if you have found a useful solution. – Erwin Brandstetter Aug 18 '15 at 12:08

Assuming a standard many-to-many implementation ...

SELECT week_id
     , count(*) FILTER (WHERE week_points > team_points) AS losses
     , count(*) FILTER (WHERE week_points < team_points) AS wins
     , count(*) FILTER (WHERE week_points = team_points) AS draws
--     , count(*) AS total -- redundant check
   SELECT week_id, team_id, sum(points) AS week_points
        , first_value(sum(points)) OVER (PARTITION BY week_id
                                         ORDER BY team_id <> 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 <> ?  -- your team_id again

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:

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

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

  • 1
    Window functions are so cool! This solution given calculates the all-play record across a given season, which we actually had a way of doing on a week-to-week basis but this absolutely blows what I had out of the water. Doing something like this SELECT SUM(count(week_points < team_points OR NULL)) OVER (ORDER BY 1) AS wins, SUM(count(week_points > team_points OR NULL)) OVER (ORDER BY 1) AS losses, SUM(count(week_points = team_points OR NULL)) OVER (ORDER BY 1) AS draws Allows me to calculate the sum of wins/losses/draws across the given season as well! Thanks! – shadefinale Aug 19 '15 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.