Postgresql query for calculating fantasy points for all players in the 2015 season from nfldb (See nfldb wiki on github for more info on setting this database up)

nfldb_fpp.sql

CREATE OR REPLACE FUNCTION nfl_ffps(_season_year int, _season_type season_phase)
RETURNS table("name" varchar, "position" text, ffps double precision) AS $$
BEGIN
  RETURN QUERY
    -- points come from my yahoo fantasy league, but can be applied arbitrarily for other leagues
    WITH kicker_ffps AS (
        SELECT
          play_player.player_id as player_id,
          play_player.kicking_xpmade as kicking_xpmade,
          (
            CASE WHEN kicking_fgm_yds BETWEEN 1 AND 39  THEN 3.00
                 WHEN kicking_fgm_yds BETWEEN 40 AND 49 THEN 4.00
                 WHEN kicking_fgm_yds >= 50 THEN 5.00
                 ELSE 0.00
            END
          ) AS kicker_fg_ffps
        FROM play_player
        LEFT JOIN game on play_player.gsis_id = game.gsis_id
        WHERE game.season_type = _season_type AND game.season_year = _season_year
    ), 
    kicker_ffps_agg AS (
        SELECT
          kicker_ffps.player_id,
          SUM(kicker_ffps.kicker_fg_ffps) + SUM(kicker_ffps.kicking_xpmade) AS ffps
        FROM kicker_ffps
        GROUP BY kicker_ffps.player_id
    ),
    defense_ffps AS (
      SELECT
        play_team.team AS full_name,
        (
          SUM(play_team.defense_sk) * 2 +
          SUM(play_team.defense_int) * 3 +
          SUM(play_team.defense_frec) * 3 +
          SUM(play_team.defense_int_tds + play_team.defense_frec_tds + play_team.defense_misc_tds) * 6 +
          SUM(play_team.defense_safe) * 2 +
          SUM(play_team.kicking_xpb + play_team.punting_blk + play_team.kicking_fgb) * 2 +
          SUM(play_team.kickret_yds + play_team.puntret_yds) * .05 +
          SUM(play_team.puntret_tds + play_team.kickret_tds) * 6
        ) AS ffps
      FROM play_player as play_team
      LEFT JOIN game ON play_team.gsis_id = game.gsis_id
      WHERE game.season_type = _season_type AND game.season_year = _season_year
      GROUP BY play_team.team
    ),
    defense_ffps_points AS (
      SELECT
        game.home_team AS full_name,
        SUM(
          CASE WHEN game.away_score = 0    THEN 10.0
               WHEN game.away_score < 7    THEN 7.0
               WHEN game.away_score < 14   THEN 4.0
               WHEN game.away_score < 21   THEN 1.0
               WHEN game.away_score < 28   THEN 0.0
               WHEN game.away_score < 35   THEN -1.0
               WHEN game.away_score >= 35  THEN -4.0
               ELSE 0
          END
        ) AS ffps
      FROM game
      WHERE game.season_type = _season_type AND game.season_year = _season_year
      GROUP BY game.home_team
      UNION ALL
      SELECT
        game.away_team AS team,
        SUM(
          CASE WHEN game.home_score = 0    THEN 10.0
               WHEN game.home_score < 7    THEN 7.0
               WHEN game.home_score < 14   THEN 4.0
               WHEN game.home_score < 21   THEN 1.0
               WHEN game.home_score < 28   THEN 0.0
               WHEN game.home_score < 35   THEN -1.0
               WHEN game.home_score >= 35  THEN -4.0
               ELSE 0
          END
        ) AS ffps
      FROM game
      WHERE game.season_type = _season_type AND game.season_year = _season_year
      GROUP BY game.away_team
    ),
    ffps_full AS (
        SELECT DISTINCT ffps_full_inner.player_id, ffps_full_inner.full_name, ffps_full_inner.position, ffps_full_inner.ffps
        FROM
        (
            SELECT player.player_id, player.full_name, player.position::text, agg_stats.ffps
            FROM
            (
            SELECT
              play_player.player_id as player_id,
              (
                SUM(play_player.passing_yds) * .04 +
                SUM(play_player.passing_tds) * 4 +
                SUM(play_player.passing_int) * -1 +
                SUM(play_player.rushing_yds) * .1 +
                SUM(play_player.rushing_tds) * 6 +
                SUM(play_player.receiving_yds) * .1 +
                SUM(play_player.receiving_tds) * 6 +
                SUM(play_player.receiving_twoptm) * 2 +
                SUM(play_player.rushing_twoptm) * 2 +
                SUM(play_player.passing_twoptm) * 2 +
                SUM(play_player.kickret_tds) * 6 +
                SUM(play_player.puntret_tds) * 6 +
                SUM(play_player.fumbles_lost) * -2 +
                SUM(play_player.fumbles_rec_tds) * 6 +
                SUM(play_player.defense_tkl_primary) * 1 +
                SUM(play_player.defense_sk) * 2 +
                SUM(play_player.defense_int) * 2 +
                SUM(play_player.defense_ffum + play_player.defense_frec) * 2 +
                SUM(play_player.defense_misc_tds + play_player.defense_frec_tds + play_player.defense_int_tds) * 6 +
                SUM(play_player.defense_safe) * 2 +
                SUM(play_player.defense_pass_def) +
                SUM(play_player.defense_fgblk) * 2 +
                SUM(play_player.defense_tkl_loss) * 1.5
              ) AS ffps
            FROM play_player
            LEFT JOIN game ON play_player.gsis_id = game.gsis_id
            WHERE game.season_type = _season_type AND game.season_year = _season_year
            GROUP BY play_player.player_id
            ) as agg_stats
            LEFT JOIN player ON agg_stats.player_id = player.player_id
            UNION ALL
            SELECT player.player_id, player.full_name, player.position::text, kicker_ffps_agg.ffps
            FROM kicker_ffps_agg
            LEFT JOIN player ON kicker_ffps_agg.player_id = player.player_id
        ) AS ffps_full_inner
    ), 
    ffps_outer AS (
      SELECT ffps_full.full_name, ffps_full.position::text, ffps_full.ffps
      FROM ffps_full
      UNION ALL
      SELECT ffps_defense.full_name, 'DEF'::text AS position, SUM(ffps_defense.ffps)
      FROM
      (
        SELECT defense_ffps.full_name, defense_ffps.ffps
        FROM defense_ffps
        UNION ALL
        SELECT defense_ffps_points.full_name, defense_ffps_points.ffps
        FROM defense_ffps_points
      ) AS ffps_defense
      GROUP BY ffps_defense.full_name
    )
    SELECT ffps_outer.full_name, ffps_outer.position, ffps_outer.ffps
    FROM ffps_outer
    WHERE ffps_outer.ffps > 25 AND ffps_outer.position <> 'UNK'
    ORDER BY ffps_outer.ffps DESC;
END;
$$ LANGUAGE plpgsql;