all_play_by_season_losses

gistfile1.txt

    WITH weekly_points_table (team_id, week_id, max_points)
    AS
    (
      SELECT teams.id, weeks.id as week_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.year = ?)
      GROUP BY teams.id, weeks.id
    )
    SELECT COUNT(*) as season_losses
    FROM weekly_points_table AS x
    WHERE x.max_points > (SELECT max_points
      FROM weekly_points_table
      WHERE team_id = ? AND x.week_id = weekly_points_table.week_id
    )