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):
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) AS ( 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 ) SELECT * 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?