Get above/below .500 point differential for NBA teams.

point-diff.rb

#!/usr/bin/env ruby

require 'pg'
require 'pp'
require 'terminal-table'
require 'csv'

conn = PGconn.open(:dbname => 'nba')

seasons = conn.exec 'select distinct(season) from teams'

seasons.to_a.each do |row|
  season = row['season']
  rows = []
  teams = conn.exec "select * from teams where season='#{season}'"
  teams.to_a.each do |t|

    team_id = t['team_id']
    team_name = t['team_name']
    abbr = t['abbr']

    above_500_teams = conn.exec "select * from teams where w_pct > 0.500 and team_id != #{team_id} and season='#{season}'"
    below_500_teams = conn.exec "select * from teams where w_pct <= 0.500 and team_id != #{team_id} and season='#{season}'"

    team_games = conn.exec "select game_id, pts as team_pts from games where team_id=#{team_id} and season='#{season}'"
    team_game_ids = team_games.to_a.map {|t| t['game_id']}

    above_500_teams_ids = above_500_teams.to_a.map {|t| t['team_id'] }
    below_500_teams_ids = below_500_teams.to_a.map {|t| t['team_id'] }

    games_above_500 = conn.exec "select games.pts as opp_team_pts, game_id, matchup, game_date from games where team_id IN(#{above_500_teams_ids.join(',')}) and game_id IN(#{team_game_ids.join(',')}) order by game_date::date"
    games_below_500 = conn.exec "select games.pts as opp_team_pts, game_id, matchup, game_date from games where team_id IN(#{below_500_teams_ids.join(',')}) and game_id IN(#{team_game_ids.join(',')}) order by game_date::date"

    above_500_diffs = []
    games_above_500.to_a.each do |g|
      tg = team_games.detect {|tg| tg['game_id'] == g['game_id']}
      diff = tg['team_pts'].to_f - g['opp_team_pts'].to_f
      above_500_diffs << diff
    end

    below_500_diffs = []
    games_below_500.to_a.each do |g|
      tg = team_games.detect {|tg| tg['game_id'] == g['game_id']}
      diff = tg['team_pts'].to_f - g['opp_team_pts'].to_f
      below_500_diffs << diff
    end

    all_games_diffs = above_500_diffs + below_500_diffs

    pm_above = above_500_diffs.inject{ |sum, el| sum + el }.to_f / above_500_diffs.size
    pm_below = below_500_diffs.inject{ |sum, el| sum + el }.to_f / below_500_diffs.size
    all = all_games_diffs.inject{ |sum, el| sum + el }.to_f / all_games_diffs.size
    above_below_dif = pm_below - pm_above

    rows << [team_name, abbr, pm_above.round(1), pm_below.round(1), above_below_dif.round(1), all.round(1)]
  end

  rows = rows.sort_by {|r| r[3]}.reverse
  rows = rows.each_with_index.map {|r, i| [i + 1] + r}

  headers = ['Rank', 'Team', 'Abbr', 'Above 500', 'Below 500', 'Point spread', 'Total Diff']
  table = Terminal::Table.new :rows => rows, :headings => headers
  puts table

  CSV.open("out/point-diff-#{season}.csv", 'w', {:headers => headers, :write_headers => true}) do |w|
    rows.each { |r| w << r }
  end
end