Many of the tables are self-explanatory. I've added comments where I think that is not the case, or there are fields of interest that would not be apparent just by looking at the schema.
The season field is the year in which the season ends, so 2016-17 would be 2017. NBA.com uses a season_id that is 20000 + the year in which the season starts, which is 22016 for the 2016-17 season.
The season_start and season_end fields are useful if you need to figure out what season a game was played in.
CREATE TABLE public.seasons
(
nbacom_season_id integer NOT NULL,
season smallint NOT NULL,
season_code character(7),
season_start date NOT NULL,
season_end date NOT NULL,
CONSTRAINT seasons_pkey PRIMARY KEY (nbacom_season_id),
CONSTRAINT seasons_season_key UNIQUE (season)
)-- team_code is unique
-- nbacom_team_id is not unique because team switches cities
-- and gets new team code while keeping old nbacom_team_id
CREATE TABLE public.teams
(
teams_id serial primary key,
nbacom_team_id integer NOT NULL,
team_code character varying(3) NOT NULL,
team_city character varying(50) NOT NULL,
team_name character varying(50) NOT NULL,
CONSTRAINT uq_team_code UNIQUE (team_code)
)CREATE TABLE public.games
(
game_id integer primary keyNOT NULL,
season smallint,
game_date date NOT NULL,
gamecode character varying(30),
visitor_team_id integer,
visitor_team_code character varying(3),
home_team_id integer,
home_team_code character varying(3),
game_type character varying(10),
CONSTRAINT games_home_team_code_fkey FOREIGN KEY (home_team_code)
REFERENCES public.teams (team_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_season_fkey FOREIGN KEY (season)
REFERENCES public.seasons (season) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_visitor_team_code_fkey FOREIGN KEY (visitor_team_code)
REFERENCES public.teams (team_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_gamecode_key UNIQUE (gamecode)
)CREATE TABLE public.players
(
players_id serial primary key,
nbacom_player_id integer NOT NULL,
first_name character varying(25) NOT NULL,
last_name character varying(25) NOT NULL,
display_first_last character varying(50) NOT NULL,
nbacom_position character varying(20) DEFAULT NULL::character varying,
primary_position character varying(2) DEFAULT NULL::character varying,
position_group character varying(5) DEFAULT NULL::character varying,
birthdate date,
school character varying(50) DEFAULT NULL::character varying,
country character varying(50) DEFAULT NULL::character varying,
last_affiliation character varying(50) DEFAULT NULL::character varying,
height smallint,
weight smallint,
jersey character varying(3) DEFAULT NULL::character varying,
from_year smallint,
to_year smallint,
draft_number smallint,
draft_round smallint,
draft_year smallint,
CONSTRAINT players_nbacom_player_id_key UNIQUE (nbacom_player_id)
)Cross-references player ids or codes from other sites with nbacom_player_id.
An example use case is that nba.com often provides incomplete position information. While positions are somewhat arbitrary, I use the 'position_group' field in players to indicate whether a player is Point, Wing, or Big. NBA.com often lists a player as 'Forward' or 'Guard', so I can't tell which group the player belongs to. ESPN assigns a specific position to a player, so by getting ESPN player information, I can update the \position_group fields in the players table.
CREATE TABLE public.player_xref
(
player_xref_id serial primary key,
nbacom_player_id integer NOT NULL,
source character varying(30) NOT NULL,
source_player_name character varying(50) NOT NULL,
source_player_id integer,
source_player_code character varying(50),
CONSTRAINT player_xref_nbacom_player_id_fkey FOREIGN KEY (nbacom_player_id)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT pid_source UNIQUE (nbacom_player_id, source)
)I calculate draftkings and fanduel points and add them to the table, even though they are not part of the nba.com API. Even if you don't play daily fantasy, they are fair (albeit flawed) proxies for season-long fantasy scoring or a metric like game score, which you might use to assess the player's combined statistical prowess in a game.
CREATE TABLE public.player_gamelogs
(
player_gamelogs_id serial primary key,
game_id integer,
nbacom_player_id integer,
player_name character varying(50),
team_id integer,
team_code character varying(3),
min smallint,
fgm smallint,
fga smallint,
fg_pct numeric,
fg3m smallint,
fg3a smallint,
fg3_pct numeric,
ftm smallint,
fta smallint,
ft_pct numeric,
oreb smallint,
dreb smallint,
reb smallint,
ast smallint,
tov smallint,
stl smallint,
blk smallint,
pf smallint,
pts smallint,
plus_minus smallint,
dk_points numeric,
fd_points numeric,
CONSTRAINT player_gamelogs_game_id_fkey FOREIGN KEY (game_id)
REFERENCES public.games (game_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT player_gamelogs_nbacom_player_id_fkey FOREIGN KEY (nbacom_player_id)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT player_gamelogs_team_code_fkey FOREIGN KEY (team_code)
REFERENCES public.teams (team_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT player_gamelogs_game_id_nbacom_player_id_key UNIQUE (game_id, nbacom_player_id)
)There are 5 types of boxscores in the nba.com API: traditional, advanced, misc, scoring, and usage. This table contains the unique fields after combining all of these boxscore types. There is some overlap with the player_gamelogs table.
It is much easier to build and maintain the player_gamelogs table because the resource has all gamelogs from a single season in a single json response. To build the player_boxscores_combined table, you have to request 5 different resources for every game_id in a season (more than 5,000 requests).
So why even bother with player_boxscores_combined? A couple of reasons: (1) there are stats that you can't get from the gamelogs, such as usage rate or how much a player relies on assists to score; (2) the 'comment' and 'start_position' fields have useful information about injuries, positions, and start/bench splits.
CREATE TABLE public.player_boxscores_combined
(
player_boxscores_combined_id serial primary key,
player_id integer,
player_name character varying,
game_id integer,
team_id integer,
ast smallint,
ast_pct numeric,
ast_ratio numeric,
ast_tov numeric,
blk smallint,
blka smallint,
comment character varying,
def_rating numeric,
dreb smallint,
dreb_pct numeric,
efg_pct numeric,
fg3a smallint,
fg3m smallint,
fg3_pct numeric,
fga smallint,
fgm smallint,
fg_pct numeric,
fta smallint,
ftm smallint,
ft_pct numeric,
min character varying(10),
min_played smallint,
net_rating numeric,
off_rating numeric,
opp_pts_2nd_chance smallint,
opp_pts_fb smallint,
opp_pts_off_tov smallint,
opp_pts_paint smallint,
oreb smallint,
oreb_pct numeric,
pace numeric,
pct_ast numeric,
pct_ast_2pm numeric,
pct_ast_3pm numeric,
pct_ast_fgm numeric,
pct_blk numeric,
pct_blka numeric,
pct_dreb numeric,
pct_fg3a numeric,
pct_fg3m numeric,
pct_fga numeric,
pct_fga_2pt numeric,
pct_fga_3pt numeric,
pct_fgm numeric,
pct_fta numeric,
pct_ftm numeric,
pct_oreb numeric,
pct_pf numeric,
pct_pfd numeric,
pct_pts numeric,
pct_pts_2pt numeric,
pct_pts_2pt_mr numeric,
pct_pts_3pt numeric,
pct_pts_fb numeric,
pct_pts_ft numeric,
pct_pts_off_tov numeric,
pct_pts_paint numeric,
pct_reb numeric,
pct_stl numeric,
pct_tov numeric,
pct_uast_2pm numeric,
pct_uast_3pm numeric,
pct_uast_fgm numeric,
pf smallint,
pfd smallint,
pie numeric,
plus_minus numeric,
pts smallint,
pts_2nd_chance smallint,
pts_fb smallint,
pts_off_tov smallint,
pts_paint smallint,
reb smallint,
reb_pct numeric,
sec_played smallint,
start_position character varying,
stl smallint,
tm_tov_pct numeric,
tov smallint,
ts_pct numeric,
usg_pct numeric,
CONSTRAINT player_boxscores_combined_game_id_fkey FOREIGN KEY (game_id)
REFERENCES public.games (game_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT player_boxscores_combined_player_id_fkey FOREIGN KEY (player_id)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uq_pid_gid UNIQUE (player_id, game_id)
)The stats in this table show the player's cumulative stats as of a specific date in the season.
As with the other tables that have an 'as_of' column, it is inclusive of that day's games. So, assume James Harden played DAL on 12-2-2016. If you want to know how he was doing prior to the game, you would set as_of to 12-1-2016.
CREATE TABLE public.playerstats_daily
(
playerstats_daily_id serial primary key,
as_of date,
season smallint,
nbacom_player_id integer,
player_name character varying(50),
team_id integer,
age numeric,
ast smallint,
ast_rank smallint,
blk smallint,
blka smallint,
blka_rank smallint,
blk_rank smallint,
dd2 smallint,
dd2_rank smallint,
dreb smallint,
dreb_rank smallint,
fg3a smallint,
fg3a_rank smallint,
fg3m smallint,
fg3m_rank smallint,
fg3_pct numeric,
fg3_pct_rank smallint,
fga smallint,
fga_rank smallint,
fgm smallint,
fgm_rank smallint,
fg_pct numeric,
fg_pct_rank smallint,
fta smallint,
fta_rank smallint,
ftm smallint,
ftm_rank smallint,
ft_pct numeric,
ft_pct_rank smallint,
gp smallint,
gp_rank smallint,
l smallint,
l_rank smallint,
min numeric,
min_played numeric,
min_rank smallint,
oreb smallint,
oreb_rank smallint,
pf smallint,
pfd smallint,
pfd_rank smallint,
pf_rank smallint,
plus_minus smallint,
plus_minus_rank smallint,
pts smallint,
pts_rank smallint,
reb smallint,
reb_rank smallint,
sec_played numeric,
stl smallint,
stl_rank smallint,
td3 smallint,
td3_rank smallint,
tov smallint,
tov_rank smallint,
w smallint,
w_pct numeric,
w_pct_rank smallint,
w_rank smallint,
reb_pct_rank smallint,
reb_pct numeric,
oreb_pct numeric,
dreb_pct numeric,
usg_pct numeric,
ast_pct numeric,
ast_ratio_rank smallint,
dreb_pct_rank smallint,
dreb_rating_rank smallint,
def_rating_rank smallint,
ts_pct_rank smallint,
ast_to_rank smallint,
tm_tov_pct_rank smallint,
ast_ratio smallint,
pace_rank smallint,
fgm_pg_rank smallint,
net_rating numeric,
ts_pct numeric,
tm_tov_pct numeric,
efg_pct_rank numeric,
fga_pg numeric,
oreb_pct_rank smallint,
off_rating numeric,
off_rating_rank smallint,
pace numeric,
def_rating numeric,
pie numeric,
ast_to numeric,
team_code character varying(10),
efg_pct numeric,
fga_pg_rank smallint,
fgm_pg smallint,
net_rating_rank smallint,
ast_pct_rank smallint,
usg_pct_rank smallint,
pie_rank smallint,
CONSTRAINT playerstats_daily_nbacom_player_id_fkey FOREIGN KEY (nbacom_player_id)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT playerstats_daily_as_of_nbacom_player_id_key UNIQUE (as_of, nbacom_player_id)
)CREATE TABLE public.team_gamelogs
(
team_gamelogs_id serial primary key,
team_id integer,
team_code character varying(3),
game_id integer,
minutes smallint,
fgm smallint,
fga smallint,
fg_pct numeric(4,3) DEFAULT NULL::numeric,
fg3m smallint,
fg3a smallint,
fg3_pct numeric(4,3) DEFAULT NULL::numeric,
ftm smallint,
fta smallint,
ft_pct numeric(4,3) DEFAULT NULL::numeric,
oreb smallint,
dreb smallint,
reb smallint,
ast smallint,
tov smallint,
stl smallint,
blk smallint,
pf smallint,
pts smallint,
plus_minus smallint,
opponent_pts smallint,
CONSTRAINT team_gamelogs_game_id_fkey FOREIGN KEY (game_id)
REFERENCES public.games (game_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT team_gamelogs_team_code_fkey FOREIGN KEY (team_code)
REFERENCES public.teams (team_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT team_gamelogs_game_id_team_id_key UNIQUE (game_id, team_id)
)CREATE TABLE public.team_boxscores_combined
(
team_boxscores_combined_id serial primary key,
game_id integer,
team_id integer,
team_code character varying(3),
ast smallint,
ast_pct numeric,
ast_ratio numeric,
ast_tov numeric,
blk smallint,
blka smallint,
def_rating numeric,
dreb smallint,
dreb_pct numeric,
efg_pct numeric,
fg3a smallint,
fg3m smallint,
fg3_pct numeric,
fga smallint,
fgm smallint,
fg_pct numeric,
fta smallint,
ftm smallint,
ft_pct numeric,
min_played smallint,
net_rating numeric,
off_rating numeric,
opp_pts_2nd_chance smallint,
opp_pts_fb smallint,
opp_pts_off_tov smallint,
opp_pts_paint smallint,
oreb smallint,
oreb_pct numeric,
pace numeric,
pct_ast_2pm numeric,
pct_ast_3pm numeric,
pct_ast_fgm numeric,
pct_fga_2pt numeric,
pct_fga_3pt numeric,
pct_pts_2pt numeric,
pct_pts_2pt_mr numeric,
pct_pts_3pt numeric,
pct_pts_fb numeric,
pct_pts_ft numeric,
pct_pts_off_tov numeric,
pct_pts_paint numeric,
pct_uast_2pm numeric,
pct_uast_3pm numeric,
pct_uast_fgm numeric,
pf smallint,
pfd smallint,
pie numeric,
plus_minus numeric,
pts smallint,
pts_2nd_chance smallint,
pts_fb smallint,
pts_off_tov smallint,
pts_paint smallint,
reb smallint,
reb_pct numeric,
stl smallint,
tm_tov_pct numeric,
tov smallint,
ts_pct numeric,
usg_pct numeric,
CONSTRAINT team_boxscores_combined_game_id_fkey FOREIGN KEY (game_id)
REFERENCES public.games (game_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT team_boxscores_combined_team_code_fkey FOREIGN KEY (team_code)
REFERENCES public.teams (team_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT tid_gid UNIQUE (team_id, game_id)
)The stats in this table show the team's cumulative stats as of a specific date in the season.
As with the other tables that have an 'as_of' column, it is inclusive of that day's games. So, assume HOU played DAL on 12-2-2016. If you want to know how the team was doing prior to the game, you would set as_of to 12-1-2016.
CREATE TABLE public.teamstats_daily
(
teamstats_daily_id serial primary key,
as_of date NOT NULL,
team_id integer,
ast numeric,
ast_pct numeric,
ast_pct_rank smallint,
ast_rank smallint,
ast_ratio numeric,
ast_ratio_rank smallint,
ast_to numeric,
ast_to_rank smallint,
blk numeric,
blka numeric,
blka_rank smallint,
blk_rank smallint,
def_rating numeric,
def_rating_rank smallint,
dreb numeric,
dreb_pct numeric,
dreb_pct_rank smallint,
dreb_rank smallint,
efg_pct numeric,
efg_pct_rank smallint,
fg3a numeric,
fg3a_rank smallint,
fg3m numeric,
fg3m_rank smallint,
fg3_pct numeric,
fg3_pct_rank smallint,
fga numeric,
fga_rank smallint,
fgm numeric,
fgm_rank smallint,
fg_pct numeric,
fg_pct_rank smallint,
fta numeric,
fta_rank smallint,
ftm numeric,
ftm_rank smallint,
ft_pct numeric,
ft_pct_rank smallint,
gp smallint,
gp_rank smallint,
l smallint,
l_rank smallint,
min numeric,
min_rank smallint,
net_rating numeric,
net_rating_rank smallint,
off_rating numeric,
off_rating_rank smallint,
oreb numeric,
oreb_pct numeric,
oreb_pct_rank smallint,
oreb_rank smallint,
pace numeric,
pace_rank smallint,
pf numeric,
pfd numeric,
pfd_rank smallint,
pf_rank smallint,
pie numeric,
pie_rank smallint,
plus_minus numeric,
plus_minus_rank smallint,
pts numeric,
pts_rank smallint,
reb numeric,
reb_pct numeric,
reb_pct_rank smallint,
reb_rank smallint,
stl numeric,
stl_rank smallint,
tm_tov_pct numeric,
tm_tov_pct_rank smallint,
tov numeric,
tov_rank smallint,
ts_pct numeric,
ts_pct_rank smallint,
w smallint,
w_pct numeric,
w_pct_rank smallint,
w_rank smallint,
CONSTRAINT teamstats_daily_as_of_team_id_key UNIQUE (as_of, team_id)
)The stats in this table show how the team's opponents fare as of a specific date in the season.
As with the other tables that have an 'as_of' column, it is inclusive of that day's games. So, assume DAL and HOU played on 12-2-2016. If you want to know how those teams defended prior to the game, you would set as_of to 12-1-2016.
CREATE TABLE public.team_opponent_dashboard
(
team_opponent_dashboard_id serial primary key,
team_id integer,
as_of date,
gp smallint,
gp_rank smallint,
l smallint,
l_rank smallint,
min numeric,
min_rank smallint,
opp_ast numeric,
opp_ast_rank smallint,
opp_blk numeric,
opp_blk_rank smallint,
opp_blka numeric,
opp_blka_rank smallint,
opp_dreb numeric,
opp_dreb_rank smallint,
opp_fg3_pct numeric,
opp_fg3_pct_rank smallint,
opp_fg3a numeric,
opp_fg3a_rank smallint,
opp_fg3m numeric,
opp_fg3m_rank smallint,
opp_fg_pct numeric,
opp_fg_pct_rank smallint,
opp_fga numeric,
opp_fga_rank smallint,
opp_fgm numeric,
opp_fgm_rank smallint,
opp_ft_pct numeric,
opp_ft_pct_rank smallint,
opp_fta numeric,
opp_fta_rank smallint,
opp_ftm numeric,
opp_ftm_rank smallint,
opp_oreb numeric,
opp_oreb_rank smallint,
opp_pf numeric,
opp_pf_rank smallint,
opp_pfd numeric,
opp_pfd_rank smallint,
opp_pts numeric,
opp_pts_rank smallint,
opp_reb numeric,
opp_reb_rank smallint,
opp_stl numeric,
opp_stl_rank smallint,
opp_tov numeric,
opp_tov_rank smallint,
plus_minus numeric,
plus_minus_rank smallint,
w smallint,
w_pct numeric,
w_pct_rank smallint,
w_rank smallint,
CONSTRAINT tid_asof UNIQUE (team_id, as_of)
)CREATE TABLE public.dfs_salaries
(
salaries_id integer serial primary key,
nbacom_player_id integer,
source_player_name character varying(50) NOT NULL,
team_code character varying,
game_date date NOT NULL,
season smallint,
source character varying(20) NOT NULL,
source_player_id integer,
source_position character(2) DEFAULT NULL::bpchar,
salary smallint NOT NULL,
dfs_position character varying,
dfs_site character varying,
CONSTRAINT dfs_salaries_nbacom_player_id_fkey FOREIGN KEY (nbacom_player_id)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT dfs_salaries_season_fkey FOREIGN KEY (season)
REFERENCES public.seasons (season) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT dfs_salaries_team_code_fkey FOREIGN KEY (team_code)
REFERENCES public.teams (team_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT dfs_salaries_nbacom_player_id_game_date_dfs_site_key UNIQUE (nbacom_player_id, game_date, dfs_site)
)This table is under active development and will likely change. The idea is to store a bunch of information about a game, such as the scores by quarter, the lines and implied totals, the referees, and the starting players.
CREATE TABLE public.games_meta
(
games_meta_id serial primary key,
nbacom_game_id integer NOT NULL,
gamecode character varying(30) NOT NULL,
game_date date NOT NULL,
team_code character varying(3) NOT NULL,
is_home boolean,
days_last_game smallint NOT NULL,
back_to_back smallint NOT NULL,
back_to_back_to_back smallint NOT NULL,
three_in_four smallint NOT NULL,
four_in_five smallint NOT NULL,
q1 smallint NOT NULL,
q2 smallint NOT NULL,
q3 smallint NOT NULL,
q4 smallint NOT NULL,
ot1 smallint,
ot2 smallint,
ot3 smallint,
ot4 smallint,
spread numeric,
game_ou numeric,
implied_total numeric,
starter1 integer,
starter2 integer,
starter3 integer,
starter4 integer,
starter5 integer,
main_referee character varying(30) DEFAULT NULL::character varying,
crew_1 character varying(30) DEFAULT NULL::character varying,
crew_2 character varying(30) DEFAULT NULL::character varying,
CONSTRAINT games_meta_gamecode_fkey FOREIGN KEY (gamecode)
REFERENCES public.games (gamecode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_meta_nbacom_game_id_fkey FOREIGN KEY (nbacom_game_id)
REFERENCES public.games (game_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_meta_starter1_fkey FOREIGN KEY (starter1)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_meta_starter2_fkey FOREIGN KEY (starter2)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_meta_starter3_fkey FOREIGN KEY (starter3)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_meta_starter4_fkey FOREIGN KEY (starter4)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_meta_starter5_fkey FOREIGN KEY (starter5)
REFERENCES public.players (nbacom_player_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT games_meta_team_code_fkey FOREIGN KEY (team_code)
REFERENCES public.teams (team_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uq_game_id_team_code UNIQUE (nbacom_game_id, team_code),
CONSTRAINT uq_gamecode_team_code UNIQUE (gamecode, team_code)
)The games table has one row per game with both teams in the same row. This view provides two rows per game, one for each team. It makes it easier to search for a team's games & opponents because you don't need to know if it was home or away.
CREATE MATERIALIZED VIEW public.teamgames AS
SELECT games.season,
games.game_id,
games.game_date,
games.gamecode,
games.visitor_team_id AS team_id,
games.visitor_team_code AS team_code,
games.home_team_id AS opponent_team_id,
games.home_team_code AS opponent_team_code,
false AS is_home
FROM games
UNION ALL
SELECT games.season,
games.game_id,
games.game_date,
games.gamecode,
games.home_team_id AS team_id,
games.home_team_code AS team_code,
games.visitor_team_id AS opponent_team_id,
games.visitor_team_code AS opponent_team_code,
true AS is_home
FROM games
WITH DATA;CREATE MATERIALIZED VIEW public.cs_games AS
SELECT games.game_id,
games.season,
games.game_date,
games.gamecode,
games.visitor_team_id,
games.visitor_team_code,
games.home_team_id,
games.home_team_code
FROM games
WHERE games.season = (SELECT max(season) from seasons)
WITH DATA;CREATE MATERIALIZED VIEW public.cs_player_gamelogs AS
SELECT * FROM player_gamelogs
WHERE (player_gamelogs.game_id IN ( SELECT cs_games.game_id
FROM cs_games))
WITH DATA;CREATE MATERIALIZED VIEW public.cs_team_gamelogs AS
SELECT * FROM team_gamelogs
WHERE (team_gamelogs.game_id IN ( SELECT cs_games.game_id
FROM cs_games))
WITH DATA;CREATE MATERIALIZED VIEW public.cs_teamgames AS
SELECT teamgames.season,
teamgames.game_id,
teamgames.game_date,
teamgames.gamecode,
teamgames.team_id,
teamgames.team_code,
teamgames.opponent_team_id,
teamgames.opponent_team_code,
teamgames.is_home
FROM teamgames
WHERE (teamgames.game_id IN ( SELECT cs_games.game_id
FROM cs_games))
WITH DATA;This shows how a player scores his fantasy points, with each category expressed as a percentage of the total.
CREATE MATERIALIZED VIEW public.dkpoints AS
WITH t1 AS (
SELECT cs_player_gamelogs.nbacom_player_id,
cs_player_gamelogs.player_name,
count(cs_player_gamelogs.player_name) AS gp,
sum(cs_player_gamelogs.min) AS mintot,
round((sum(cs_player_gamelogs.min) / count(cs_player_gamelogs.player_name))::numeric, 1) AS mpg,
round(sum(cs_player_gamelogs.dk_points) / count(cs_player_gamelogs.player_name)::numeric, 2) AS dkpg,
round(sum(cs_player_gamelogs.dk_points) / sum(cs_player_gamelogs.min)::numeric, 2) AS dkmin,
round(sum(cs_player_gamelogs.pts)::numeric / sum(cs_player_gamelogs.dk_points), 3) AS ptspct,
round(sum(cs_player_gamelogs.reb)::numeric * 1.25 / sum(cs_player_gamelogs.dk_points), 3) AS rebpct,
round(sum(cs_player_gamelogs.ast)::numeric * 1.5 / sum(cs_player_gamelogs.dk_points), 3) AS astpct,
round((sum(cs_player_gamelogs.stl) * 2)::numeric / sum(cs_player_gamelogs.dk_points), 3) AS stlpct,
round((sum(cs_player_gamelogs.blk) * 2)::numeric / sum(cs_player_gamelogs.dk_points), 3) AS blkpct,
round(sum(cs_player_gamelogs.tov)::numeric * '-0.5'::numeric / sum(cs_player_gamelogs.dk_points), 3) AS tovpct,
round(sum(cs_player_gamelogs.fg3m)::numeric * 0.5 / sum(cs_player_gamelogs.dk_points), 3) AS tpmpct
FROM cs_player_gamelogs
GROUP BY cs_player_gamelogs.nbacom_player_id, cs_player_gamelogs.player_name
)
SELECT t1.*,
1::numeric - (t1.ptspct + t1.rebpct + t1.astpct + t1.stlpct + t1.blkpct + t1.tovpct + t1.tpmpct) AS bonuspct
FROM t1
WITH DATA;CREATE OR REPLACE FUNCTION dfs.dk_points(
pts integer, fg3m integer, reb integer, ast integer, stl integer, blk integer, tov integer)
RETURNS numeric AS
$BODY$
dkpts = pts + (fg3m * .5) + (reb * 1.25) + (ast * 1.5) + (stl * 2) + (blk * 2) - (tov * .5)
over_ten = 0
if pts >= 10:
over_ten += 1
if fg3m >= 10:
over_ten += 1
if reb >= 10:
over_ten += 1
if ast >= 10:
over_ten += 1
if stl >= 10:
over_ten += 1
if blk >= 10:
over_ten += 1
if over_ten == 2:
dkpts += 1.5
elif over_ten >= 3:
dkpts += 4.5
return round(dkpts, 5)
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;CREATE OR REPLACE FUNCTION dfs.fd_points(
pts integer,
reb integer,
ast integer,
stl integer,
blk integer,
tov integer)
RETURNS numeric AS
$BODY$
fdpts = pts + (reb * 1.2) + (ast * 1.5) + (stl * 2) + (blk * 2) - tov
return round(fdpts, 5)
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;CREATE OR REPLACE FUNCTION stats.refresh_cs_player_gamelogs()
RETURNS trigger AS
$BODY$
BEGIN
REFRESH MATERIALIZED VIEW stats.cs_player_gamelogs;
RETURN null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;