bigquery/bqml/resources/feature_input_query.sql

-- Copyright 2021 Google LLC
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

# This query creates a sample table using
# the ncaa_basketball public dataset.  It
# uses a format string token for setting
# the destination dataset.
CREATE OR REPLACE TABLE `{0}.cume_games` AS
SELECT
  game_id,
  season,
  scheduled_date,
  home_team,
  market as school_name,
  CONCAT(CAST(season AS STRING), ":", team_id) AS team_id,
  conf_name,
  division_name,
  minutes,
  points,
  fast_break_pts,
  second_chance_pts,
  field_goals_made,
  field_goals_att,
  field_goals_pct,
  three_points_made,
  three_points_att,
  three_points_pct,
  two_points_made,
  two_points_att,
  two_points_pct,
  free_throws_made,
  free_throws_att,
  free_throws_pct,
  ts_pct,
  efg_pct,
  rebounds,
  offensive_rebounds,
  defensive_rebounds,
  dreb_pct,
  oreb_pct,
  steals,
  blocks,
  blocked_att,
  assists,
  turnovers,
  team_turnovers,
  points_off_turnovers,
  assists_turnover_ratio,
  ast_fgm_pct,
  personal_fouls,
  flagrant_fouls,
  player_tech_fouls,
  team_tech_fouls,
  coach_tech_fouls,
  ejections,
  foulouts,
  score_delta,
  opp_score_delta,
  possessions,
  ROW_NUMBER() OVER(partition by season, team_id order by scheduled_date ASC) AS game_number,
  SUM(is_win) OVER(partition by season, team_id order by scheduled_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS wins,
  SUM(is_loss) OVER(partition by season, team_id order by scheduled_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS losses,
  ROUND(AVG(points) OVER w1, 2) AS  points_avg_last_1,
  ROUND(AVG(points) OVER w5, 2) AS  points_avg_last_5,
  ROUND(AVG(points) OVER w10, 2) AS  points_avg_last_10,
  ROUND(AVG(fast_break_pts) OVER w1, 2) AS  fast_break_pts_avg_last_1,
  ROUND(AVG(fast_break_pts) OVER w5, 2) AS  fast_break_pts_avg_last_5,
  ROUND(AVG(fast_break_pts) OVER w10, 2) AS fast_break_pts_avg_last_10,
  ROUND(AVG(second_chance_pts) OVER w1, 2) AS  second_chance_pts_avg_last_1,
  ROUND(AVG(second_chance_pts) OVER w5, 2) AS  second_chance_pts_avg_last_5,
  ROUND(AVG(second_chance_pts) OVER w10, 2) AS second_chance_pts_avg_last_10,
  ROUND(AVG(field_goals_made) OVER w1, 2) AS  field_goals_made_avg_last_1,
  ROUND(AVG(field_goals_made) OVER w5, 2) AS  field_goals_made_avg_last_5,
  ROUND(AVG(field_goals_made) OVER w10, 2) AS field_goals_made_avg_last_10,
  ROUND(AVG(field_goals_att) OVER w1, 2) AS  field_goals_att_avg_last_1,
  ROUND(AVG(field_goals_att) OVER w5, 2) AS  field_goals_att_avg_last_5,
  ROUND(AVG(field_goals_att) OVER w10, 2) AS field_goals_att_avg_last_10,
  ROUND(AVG(field_goals_pct) OVER w1, 2) AS  field_goals_pct_avg_last_1,
  ROUND(AVG(field_goals_pct) OVER w5, 2) AS  field_goals_pct_avg_last_5,
  ROUND(AVG(field_goals_pct) OVER w10, 2) AS field_goals_pct_avg_last_10,
  ROUND(AVG(three_points_made) OVER w1, 2) AS  three_points_made_avg_last_1,
  ROUND(AVG(three_points_made) OVER w5, 2) AS  three_points_made_avg_last_5,
  ROUND(AVG(three_points_made) OVER w10, 2) AS three_points_made_avg_last_10,
  ROUND(AVG(three_points_att) OVER w1, 2) AS  three_points_att_avg_last_1,
  ROUND(AVG(three_points_att) OVER w5, 2) AS  three_points_att_avg_last_5,
  ROUND(AVG(three_points_att) OVER w10, 2) AS three_points_att_avg_last_10,
  ROUND(AVG(three_points_pct) OVER w1, 2) AS  three_points_pct_avg_last_1,
  ROUND(AVG(three_points_pct) OVER w5, 2) AS  three_points_pct_avg_last_5,
  ROUND(AVG(three_points_pct) OVER w10, 2) AS three_points_pct_avg_last_10,
  ROUND(AVG(two_points_made) OVER w1, 2) AS  two_points_made_avg_last_1,
  ROUND(AVG(two_points_made) OVER w5, 2) AS  two_points_made_avg_last_5,
  ROUND(AVG(two_points_made) OVER w10, 2) AS two_points_made_avg_last_10,
  ROUND(AVG(two_points_att) OVER w1, 2) AS  two_points_att_avg_last_1,
  ROUND(AVG(two_points_att) OVER w5, 2) AS  two_points_att_avg_last_5,
  ROUND(AVG(two_points_att) OVER w10, 2) AS two_points_att_avg_last_10,
  ROUND(AVG(two_points_pct) OVER w1, 2) AS  two_points_pct_avg_last_1,
  ROUND(AVG(two_points_pct) OVER w5, 2) AS  two_points_pct_avg_last_5,
  ROUND(AVG(two_points_pct) OVER w10, 2) AS two_points_pct_avg_last_10,
  ROUND(AVG(free_throws_made) OVER w1, 2) AS  free_throws_made_avg_last_1,
  ROUND(AVG(free_throws_made) OVER w5, 2) AS  free_throws_made_avg_last_5,
  ROUND(AVG(free_throws_made) OVER w10, 2) AS free_throws_made_avg_last_10,
  ROUND(AVG(free_throws_att) OVER w1, 2) AS  free_throws_att_avg_last_1,
  ROUND(AVG(free_throws_att) OVER w5, 2) AS  free_throws_att_avg_last_5,
  ROUND(AVG(free_throws_att) OVER w10, 2) AS free_throws_att_avg_last_10,
  ROUND(AVG(free_throws_pct) OVER w1, 2) AS  free_throws_pct_avg_last_1,
  ROUND(AVG(free_throws_pct) OVER w5, 2) AS  free_throws_pct_avg_last_5,
  ROUND(AVG(free_throws_pct) OVER w10, 2) AS free_throws_pct_avg_last_10,
  ROUND(AVG(ts_pct) OVER w1, 2) AS  ts_pct_avg_last_1,
  ROUND(AVG(ts_pct) OVER w5, 2) AS  ts_pct_avg_last_5,
  ROUND(AVG(ts_pct) OVER w10, 2) AS ts_pct_avg_last_10,
  ROUND(AVG(efg_pct) OVER w1, 2) AS  efg_pct_avg_last_1,
  ROUND(AVG(efg_pct) OVER w5, 2) AS  efg_pct_avg_last_5,
  ROUND(AVG(efg_pct) OVER w10, 2) AS efg_pct_avg_last_10,
  ROUND(AVG(rebounds) OVER w1, 2) AS  rebounds_avg_last_1,
  ROUND(AVG(rebounds) OVER w5, 2) AS  rebounds_avg_last_5,
  ROUND(AVG(rebounds) OVER w10, 2) AS rebounds_avg_last_10,
  ROUND(AVG(offensive_rebounds) OVER w1, 2) AS  offensive_rebounds_avg_last_1,
  ROUND(AVG(offensive_rebounds) OVER w5, 2) AS  offensive_rebounds_avg_last_5,
  ROUND(AVG(offensive_rebounds) OVER w10, 2) AS offensive_rebounds_avg_last_10,
  ROUND(AVG(defensive_rebounds) OVER w1, 2) AS  defensive_rebounds_avg_last_1,
  ROUND(AVG(defensive_rebounds) OVER w5, 2) AS  defensive_rebounds_avg_last_5,
  ROUND(AVG(defensive_rebounds) OVER w10, 2) AS defensive_rebounds_avg_last_10,
  ROUND(AVG(dreb_pct) OVER w1, 2) AS  dreb_pct_avg_last_1,
  ROUND(AVG(dreb_pct) OVER w5, 2) AS  dreb_pct_avg_last_5,
  ROUND(AVG(dreb_pct) OVER w10, 2) AS dreb_pct_avg_last_10,
  ROUND(AVG(oreb_pct) OVER w1, 2) AS  oreb_pct_avg_last_1,
  ROUND(AVG(oreb_pct) OVER w5, 2) AS  oreb_pct_avg_last_5,
  ROUND(AVG(oreb_pct) OVER w10, 2) AS oreb_pct_avg_last_10,
  ROUND(AVG(steals) OVER w1, 2) AS  steals_avg_last_1,
  ROUND(AVG(steals) OVER w5, 2) AS  steals_avg_last_5,
  ROUND(AVG(steals) OVER w10, 2) AS steals_avg_last_10,
  ROUND(AVG(blocks) OVER w1, 2) AS  blocks_avg_last_1,
  ROUND(AVG(blocks) OVER w5, 2) AS  blocks_avg_last_5,
  ROUND(AVG(blocks) OVER w10, 2) AS blocks_avg_last_10,
  ROUND(AVG(assists) OVER w1, 2) AS  assists_avg_last_1,
  ROUND(AVG(assists) OVER w5, 2) AS  assists_avg_last_5,
  ROUND(AVG(assists) OVER w10, 2) AS assists_avg_last_10,
  ROUND(AVG(turnovers) OVER w1, 2) AS  turnovers_avg_last_1,
  ROUND(AVG(turnovers) OVER w5, 2) AS  turnovers_avg_last_5,
  ROUND(AVG(turnovers) OVER w10, 2) AS turnovers_avg_last_10,
  ROUND(AVG(team_turnovers) OVER w1, 2) AS  team_turnovers_avg_last_1,
  ROUND(AVG(team_turnovers) OVER w5, 2) AS  team_turnovers_avg_last_5,
  ROUND(AVG(team_turnovers) OVER w10, 2) AS team_turnovers_avg_last_10,
  ROUND(AVG(points_off_turnovers) OVER w1, 2) AS  points_off_turnovers_avg_last_1,
  ROUND(AVG(points_off_turnovers) OVER w5, 2) AS  points_off_turnovers_avg_last_5,
  ROUND(AVG(points_off_turnovers) OVER w10, 2) AS points_off_turnovers_avg_last_10,
  ROUND(AVG(assists_turnover_ratio) OVER w1, 2) AS  assists_turnover_ratio_avg_last_1,
  ROUND(AVG(assists_turnover_ratio) OVER w5, 2) AS  assists_turnover_ratio_avg_last_5,
  ROUND(AVG(assists_turnover_ratio) OVER w10, 2) AS assists_turnover_ratio_avg_last_10,
  ROUND(AVG(ast_fgm_pct) OVER w1, 2) AS  ast_fgm_pct_avg_last_1,
  ROUND(AVG(ast_fgm_pct) OVER w5, 2) AS  ast_fgm_pct_avg_last_5,
  ROUND(AVG(ast_fgm_pct) OVER w10, 2) AS ast_fgm_pct_avg_last_10,
  ROUND(AVG(personal_fouls) OVER w1, 2) AS  personal_fouls_avg_last_1,
  ROUND(AVG(personal_fouls) OVER w5, 2) AS  personal_fouls_avg_last_5,
  ROUND(AVG(personal_fouls) OVER w10, 2) AS personal_fouls_avg_last_10,
  ROUND(AVG(flagrant_fouls) OVER w1, 2) AS  flagrant_fouls_avg_last_1,
  ROUND(AVG(flagrant_fouls) OVER w5, 2) AS  flagrant_fouls_avg_last_5,
  ROUND(AVG(flagrant_fouls) OVER w10, 2) AS flagrant_fouls_avg_last_10,
  ROUND(AVG(player_tech_fouls) OVER w1, 2) AS  player_tech_fouls_avg_last_1,
  ROUND(AVG(player_tech_fouls) OVER w5, 2) AS  player_tech_fouls_avg_last_5,
  ROUND(AVG(player_tech_fouls) OVER w10, 2) AS player_tech_fouls_avg_last_10,
  ROUND(AVG(team_tech_fouls) OVER w1, 2) AS  team_tech_fouls_avg_last_1,
  ROUND(AVG(team_tech_fouls) OVER w5, 2) AS  team_tech_fouls_avg_last_5,
  ROUND(AVG(team_tech_fouls) OVER w10, 2) AS team_tech_fouls_avg_last_10,
  ROUND(AVG(coach_tech_fouls) OVER w1, 2) AS  coach_tech_fouls_avg_last_1,
  ROUND(AVG(coach_tech_fouls) OVER w5, 2) AS  coach_tech_fouls_avg_last_5,
  ROUND(AVG(coach_tech_fouls) OVER w10, 2) AS coach_tech_fouls_avg_last_10,
  ROUND(AVG(ejections) OVER w1, 2) AS  ejections_avg_last_1,
  ROUND(AVG(ejections) OVER w5, 2) AS  ejections_avg_last_5,
  ROUND(AVG(ejections) OVER w10, 2) AS ejections_avg_last_10,
  ROUND(AVG(foulouts) OVER w1, 2) AS  foulouts_avg_last_1,
  ROUND(AVG(foulouts) OVER w5, 2) AS  foulouts_avg_last_5,
  ROUND(AVG(foulouts) OVER w10, 2) AS foulouts_avg_last_10,
  ROUND(AVG(score_delta) OVER w1, 2) AS  score_delta_avg_last_1,
  ROUND(AVG(score_delta) OVER w5, 2) AS  score_delta_avg_last_5,
  ROUND(AVG(score_delta) OVER w10, 2) AS score_delta_avg_last_10,
  ROUND(AVG(possessions) OVER w1, 2) AS  possessions_avg_last_1,
  ROUND(AVG(possessions) OVER w5, 2) AS  possessions_avg_last_5,
  ROUND(AVG(possessions) OVER w10, 2) AS possessions_avg_last_10,
  ROUND(AVG(opp_points) OVER w1, 2) AS  opp_points_avg_last_1,
  ROUND(AVG(opp_points) OVER w5, 2) AS  opp_points_avg_last_5,
  ROUND(AVG(opp_points) OVER w10, 2) AS  opp_points_avg_last_10,
  ROUND(AVG(opp_fast_break_pts) OVER w1, 2) AS  opp_fast_break_pts_avg_last_1,
  ROUND(AVG(opp_fast_break_pts) OVER w5, 2) AS  opp_fast_break_pts_avg_last_5,
  ROUND(AVG(opp_fast_break_pts) OVER w10, 2) AS opp_fast_break_pts_avg_last_10,
  ROUND(AVG(opp_second_chance_pts) OVER w1, 2) AS  opp_second_chance_pts_avg_last_1,
  ROUND(AVG(opp_second_chance_pts) OVER w5, 2) AS  opp_second_chance_pts_avg_last_5,
  ROUND(AVG(opp_second_chance_pts) OVER w10, 2) AS opp_second_chance_pts_avg_last_10,
  ROUND(AVG(opp_field_goals_made) OVER w1, 2) AS  opp_field_goals_made_avg_last_1,
  ROUND(AVG(opp_field_goals_made) OVER w5, 2) AS  opp_field_goals_made_avg_last_5,
  ROUND(AVG(opp_field_goals_made) OVER w10, 2) AS opp_field_goals_made_avg_last_10,
  ROUND(AVG(opp_field_goals_att) OVER w1, 2) AS  opp_field_goals_att_avg_last_1,
  ROUND(AVG(opp_field_goals_att) OVER w5, 2) AS  opp_field_goals_att_avg_last_5,
  ROUND(AVG(opp_field_goals_att) OVER w10, 2) AS opp_field_goals_att_avg_last_10,
  ROUND(AVG(opp_field_goals_pct) OVER w1, 2) AS  opp_field_goals_pct_avg_last_1,
  ROUND(AVG(opp_field_goals_pct) OVER w5, 2) AS  opp_field_goals_pct_avg_last_5,
  ROUND(AVG(opp_field_goals_pct) OVER w10, 2) AS opp_field_goals_pct_avg_last_10,
  ROUND(AVG(opp_three_points_made) OVER w1, 2) AS  opp_three_points_made_avg_last_1,
  ROUND(AVG(opp_three_points_made) OVER w5, 2) AS  opp_three_points_made_avg_last_5,
  ROUND(AVG(opp_three_points_made) OVER w10, 2) AS opp_three_points_made_avg_last_10,
  ROUND(AVG(opp_three_points_att) OVER w1, 2) AS  opp_three_points_att_avg_last_1,
  ROUND(AVG(opp_three_points_att) OVER w5, 2) AS  opp_three_points_att_avg_last_5,
  ROUND(AVG(opp_three_points_att) OVER w10, 2) AS opp_three_points_att_avg_last_10,
  ROUND(AVG(opp_three_points_pct) OVER w1, 2) AS  opp_three_points_pct_avg_last_1,
  ROUND(AVG(opp_three_points_pct) OVER w5, 2) AS  opp_three_points_pct_avg_last_5,
  ROUND(AVG(opp_three_points_pct) OVER w10, 2) AS opp_three_points_pct_avg_last_10,
  ROUND(AVG(opp_two_points_made) OVER w1, 2) AS  opp_two_points_made_avg_last_1,
  ROUND(AVG(opp_two_points_made) OVER w5, 2) AS  opp_two_points_made_avg_last_5,
  ROUND(AVG(opp_two_points_made) OVER w10, 2) AS opp_two_points_made_avg_last_10,
  ROUND(AVG(opp_two_points_att) OVER w1, 2) AS  opp_two_points_att_avg_last_1,
  ROUND(AVG(opp_two_points_att) OVER w5, 2) AS  opp_two_points_att_avg_last_5,
  ROUND(AVG(opp_two_points_att) OVER w10, 2) AS opp_two_points_att_avg_last_10,
  ROUND(AVG(opp_two_points_pct) OVER w1, 2) AS  opp_two_points_pct_avg_last_1,
  ROUND(AVG(opp_two_points_pct) OVER w5, 2) AS  opp_two_points_pct_avg_last_5,
  ROUND(AVG(opp_two_points_pct) OVER w10, 2) AS opp_two_points_pct_avg_last_10,
  ROUND(AVG(opp_free_throws_made) OVER w1, 2) AS  opp_free_throws_made_avg_last_1,
  ROUND(AVG(opp_free_throws_made) OVER w5, 2) AS  opp_free_throws_made_avg_last_5,
  ROUND(AVG(opp_free_throws_made) OVER w10, 2) AS opp_free_throws_made_avg_last_10,
  ROUND(AVG(opp_free_throws_att) OVER w1, 2) AS  opp_free_throws_att_avg_last_1,
  ROUND(AVG(opp_free_throws_att) OVER w5, 2) AS  opp_free_throws_att_avg_last_5,
  ROUND(AVG(opp_free_throws_att) OVER w10, 2) AS opp_free_throws_att_avg_last_10,
  ROUND(AVG(opp_free_throws_pct) OVER w1, 2) AS  opp_free_throws_pct_avg_last_1,
  ROUND(AVG(opp_free_throws_pct) OVER w5, 2) AS  opp_free_throws_pct_avg_last_5,
  ROUND(AVG(opp_free_throws_pct) OVER w10, 2) AS opp_free_throws_pct_avg_last_10,
  ROUND(AVG(opp_ts_pct) OVER w1, 2) AS  opp_ts_pct_avg_last_1,
  ROUND(AVG(opp_ts_pct) OVER w5, 2) AS  opp_ts_pct_avg_last_5,
  ROUND(AVG(opp_ts_pct) OVER w10, 2) AS opp_ts_pct_avg_last_10,
  ROUND(AVG(opp_efg_pct) OVER w1, 2) AS  opp_efg_pct_avg_last_1,
  ROUND(AVG(opp_efg_pct) OVER w5, 2) AS  opp_efg_pct_avg_last_5,
  ROUND(AVG(opp_efg_pct) OVER w10, 2) AS opp_efg_pct_avg_last_10,
  ROUND(AVG(opp_rebounds) OVER w1, 2) AS  opp_rebounds_avg_last_1,
  ROUND(AVG(opp_rebounds) OVER w5, 2) AS  opp_rebounds_avg_last_5,
  ROUND(AVG(opp_rebounds) OVER w10, 2) AS opp_rebounds_avg_last_10,
  ROUND(AVG(opp_offensive_rebounds) OVER w1, 2) AS  opp_offensive_rebounds_avg_last_1,
  ROUND(AVG(opp_offensive_rebounds) OVER w5, 2) AS  opp_offensive_rebounds_avg_last_5,
  ROUND(AVG(opp_offensive_rebounds) OVER w10, 2) AS opp_offensive_rebounds_avg_last_10,
  ROUND(AVG(opp_defensive_rebounds) OVER w1, 2) AS  opp_defensive_rebounds_avg_last_1,
  ROUND(AVG(opp_defensive_rebounds) OVER w5, 2) AS  opp_defensive_rebounds_avg_last_5,
  ROUND(AVG(opp_defensive_rebounds) OVER w10, 2) AS opp_defensive_rebounds_avg_last_10,
  ROUND(AVG(opp_dreb_pct) OVER w1, 2) AS  opp_dreb_pct_avg_last_1,
  ROUND(AVG(opp_dreb_pct) OVER w5, 2) AS  opp_dreb_pct_avg_last_5,
  ROUND(AVG(opp_dreb_pct) OVER w10, 2) AS opp_dreb_pct_avg_last_10,
  ROUND(AVG(opp_oreb_pct) OVER w1, 2) AS  opp_oreb_pct_avg_last_1,
  ROUND(AVG(opp_oreb_pct) OVER w5, 2) AS  opp_oreb_pct_avg_last_5,
  ROUND(AVG(opp_oreb_pct) OVER w10, 2) AS opp_oreb_pct_avg_last_10,
  ROUND(AVG(opp_steals) OVER w1, 2) AS  opp_steals_avg_last_1,
  ROUND(AVG(opp_steals) OVER w5, 2) AS  opp_steals_avg_last_5,
  ROUND(AVG(opp_steals) OVER w10, 2) AS opp_steals_avg_last_10,
  ROUND(AVG(opp_blocks) OVER w1, 2) AS  opp_blocks_avg_last_1,
  ROUND(AVG(opp_blocks) OVER w5, 2) AS  opp_blocks_avg_last_5,
  ROUND(AVG(opp_blocks) OVER w10, 2) AS opp_blocks_avg_last_10,
  ROUND(AVG(opp_assists) OVER w1, 2) AS  opp_assists_avg_last_1,
  ROUND(AVG(opp_assists) OVER w5, 2) AS  opp_assists_avg_last_5,
  ROUND(AVG(opp_assists) OVER w10, 2) AS opp_assists_avg_last_10,
  ROUND(AVG(opp_turnovers) OVER w1, 2) AS  opp_turnovers_avg_last_1,
  ROUND(AVG(opp_turnovers) OVER w5, 2) AS  opp_turnovers_avg_last_5,
  ROUND(AVG(opp_turnovers) OVER w10, 2) AS opp_turnovers_avg_last_10,
  ROUND(AVG(opp_team_turnovers) OVER w1, 2) AS  opp_team_turnovers_avg_last_1,
  ROUND(AVG(opp_team_turnovers) OVER w5, 2) AS  opp_team_turnovers_avg_last_5,
  ROUND(AVG(opp_team_turnovers) OVER w10, 2) AS opp_team_turnovers_avg_last_10,
  ROUND(AVG(opp_points_off_turnovers) OVER w1, 2) AS  opp_points_off_turnovers_avg_last_1,
  ROUND(AVG(opp_points_off_turnovers) OVER w5, 2) AS  opp_points_off_turnovers_avg_last_5,
  ROUND(AVG(opp_points_off_turnovers) OVER w10, 2) AS opp_points_off_turnovers_avg_last_10,
  ROUND(AVG(opp_assists_turnover_ratio) OVER w1, 2) AS  opp_assists_turnover_ratio_avg_last_1,
  ROUND(AVG(opp_assists_turnover_ratio) OVER w5, 2) AS  opp_assists_turnover_ratio_avg_last_5,
  ROUND(AVG(opp_assists_turnover_ratio) OVER w10, 2) AS opp_assists_turnover_ratio_avg_last_10,
  ROUND(AVG(opp_ast_fgm_pct) OVER w1, 2) AS  opp_ast_fgm_pct_avg_last_1,
  ROUND(AVG(opp_ast_fgm_pct) OVER w5, 2) AS  opp_ast_fgm_pct_avg_last_5,
  ROUND(AVG(opp_ast_fgm_pct) OVER w10, 2) AS opp_ast_fgm_pct_avg_last_10,
  ROUND(AVG(opp_personal_fouls) OVER w1, 2) AS  opp_personal_fouls_avg_last_1,
  ROUND(AVG(opp_personal_fouls) OVER w5, 2) AS  opp_personal_fouls_avg_last_5,
  ROUND(AVG(opp_personal_fouls) OVER w10, 2) AS opp_personal_fouls_avg_last_10,
  ROUND(AVG(opp_flagrant_fouls) OVER w1, 2) AS  opp_flagrant_fouls_avg_last_1,
  ROUND(AVG(opp_flagrant_fouls) OVER w5, 2) AS  opp_flagrant_fouls_avg_last_5,
  ROUND(AVG(opp_flagrant_fouls) OVER w10, 2) AS opp_flagrant_fouls_avg_last_10,
  ROUND(AVG(opp_player_tech_fouls) OVER w1, 2) AS  opp_player_tech_fouls_avg_last_1,
  ROUND(AVG(opp_player_tech_fouls) OVER w5, 2) AS  opp_player_tech_fouls_avg_last_5,
  ROUND(AVG(opp_player_tech_fouls) OVER w10, 2) AS opp_player_tech_fouls_avg_last_10,
  ROUND(AVG(opp_team_tech_fouls) OVER w1, 2) AS  opp_team_tech_fouls_avg_last_1,
  ROUND(AVG(opp_team_tech_fouls) OVER w5, 2) AS  opp_team_tech_fouls_avg_last_5,
  ROUND(AVG(opp_team_tech_fouls) OVER w10, 2) AS opp_team_tech_fouls_avg_last_10,
  ROUND(AVG(opp_coach_tech_fouls) OVER w1, 2) AS  opp_coach_tech_fouls_avg_last_1,
  ROUND(AVG(opp_coach_tech_fouls) OVER w5, 2) AS  opp_coach_tech_fouls_avg_last_5,
  ROUND(AVG(opp_coach_tech_fouls) OVER w10, 2) AS opp_coach_tech_fouls_avg_last_10,
  ROUND(AVG(opp_ejections) OVER w1, 2) AS  opp_ejections_avg_last_1,
  ROUND(AVG(opp_ejections) OVER w5, 2) AS  opp_ejections_avg_last_5,
  ROUND(AVG(opp_ejections) OVER w10, 2) AS opp_ejections_avg_last_10,
  ROUND(AVG(opp_foulouts) OVER w1, 2) AS  opp_foulouts_avg_last_1,
  ROUND(AVG(opp_foulouts) OVER w5, 2) AS  opp_foulouts_avg_last_5,
  ROUND(AVG(opp_foulouts) OVER w10, 2) AS opp_foulouts_avg_last_10,
  ROUND(AVG(opp_score_delta) OVER w1, 2) AS  opp_score_delta_avg_last_1,
  ROUND(AVG(opp_score_delta) OVER w5, 2) AS  opp_score_delta_avg_last_5,
  ROUND(AVG(opp_score_delta) OVER w10, 2) AS opp_score_delta_avg_last_10,
  ROUND(AVG(opp_possessions) OVER w1, 2) AS  opp_possessions_avg_last_1,
  ROUND(AVG(opp_possessions) OVER w5, 2) AS  opp_possessions_avg_last_5,
  ROUND(AVG(opp_possessions) OVER w10, 2) AS opp_possessions_avg_last_10,
  ROUND(STDDEV_POP(points) OVER w5, 2) AS  points_std_last_5,
  ROUND(STDDEV_POP(points) OVER w10, 2) AS  points_std_last_10,
  ROUND(STDDEV_POP(fast_break_pts) OVER w5, 2) AS  fast_break_pts_std_last_5,
  ROUND(STDDEV_POP(fast_break_pts) OVER w10, 2) AS fast_break_pts_std_last_10,
  ROUND(STDDEV_POP(second_chance_pts) OVER w5, 2) AS  second_chance_pts_std_last_5,
  ROUND(STDDEV_POP(second_chance_pts) OVER w10, 2) AS second_chance_pts_std_last_10,
  ROUND(STDDEV_POP(field_goals_made) OVER w5, 2) AS  field_goals_made_std_last_5,
  ROUND(STDDEV_POP(field_goals_made) OVER w10, 2) AS field_goals_made_std_last_10,
  ROUND(STDDEV_POP(field_goals_att) OVER w5, 2) AS  field_goals_att_std_last_5,
  ROUND(STDDEV_POP(field_goals_att) OVER w10, 2) AS field_goals_att_std_last_10,
  ROUND(STDDEV_POP(field_goals_pct) OVER w5, 2) AS  field_goals_pct_std_last_5,
  ROUND(STDDEV_POP(field_goals_pct) OVER w10, 2) AS field_goals_pct_std_last_10,
  ROUND(STDDEV_POP(three_points_made) OVER w5, 2) AS  three_points_made_std_last_5,
  ROUND(STDDEV_POP(three_points_made) OVER w10, 2) AS three_points_made_std_last_10,
  ROUND(STDDEV_POP(three_points_att) OVER w5, 2) AS  three_points_att_std_last_5,
  ROUND(STDDEV_POP(three_points_att) OVER w10, 2) AS three_points_att_std_last_10,
  ROUND(STDDEV_POP(three_points_pct) OVER w5, 2) AS  three_points_pct_std_last_5,
  ROUND(STDDEV_POP(three_points_pct) OVER w10, 2) AS three_points_pct_std_last_10,
  ROUND(STDDEV_POP(two_points_made) OVER w5, 2) AS  two_points_made_std_last_5,
  ROUND(STDDEV_POP(two_points_made) OVER w10, 2) AS two_points_made_std_last_10,
  ROUND(STDDEV_POP(two_points_att) OVER w5, 2) AS  two_points_att_std_last_5,
  ROUND(STDDEV_POP(two_points_att) OVER w10, 2) AS two_points_att_std_last_10,
  ROUND(STDDEV_POP(two_points_pct) OVER w5, 2) AS  two_points_pct_std_last_5,
  ROUND(STDDEV_POP(two_points_pct) OVER w10, 2) AS two_points_pct_std_last_10,
  ROUND(STDDEV_POP(free_throws_made) OVER w5, 2) AS  free_throws_made_std_last_5,
  ROUND(STDDEV_POP(free_throws_made) OVER w10, 2) AS free_throws_made_std_last_10,
  ROUND(STDDEV_POP(free_throws_att) OVER w5, 2) AS  free_throws_att_std_last_5,
  ROUND(STDDEV_POP(free_throws_att) OVER w10, 2) AS free_throws_att_std_last_10,
  ROUND(STDDEV_POP(free_throws_pct) OVER w5, 2) AS  free_throws_pct_std_last_5,
  ROUND(STDDEV_POP(free_throws_pct) OVER w10, 2) AS free_throws_pct_std_last_10,
  ROUND(STDDEV_POP(ts_pct) OVER w5, 2) AS  ts_pct_std_last_5,
  ROUND(STDDEV_POP(ts_pct) OVER w10, 2) AS ts_pct_std_last_10,
  ROUND(STDDEV_POP(efg_pct) OVER w5, 2) AS  efg_pct_std_last_5,
  ROUND(STDDEV_POP(efg_pct) OVER w10, 2) AS efg_pct_std_last_10,
  ROUND(STDDEV_POP(rebounds) OVER w5, 2) AS  rebounds_std_last_5,
  ROUND(STDDEV_POP(rebounds) OVER w10, 2) AS rebounds_std_last_10,
  ROUND(STDDEV_POP(offensive_rebounds) OVER w5, 2) AS  offensive_rebounds_std_last_5,
  ROUND(STDDEV_POP(offensive_rebounds) OVER w10, 2) AS offensive_rebounds_std_last_10,
  ROUND(STDDEV_POP(defensive_rebounds) OVER w5, 2) AS  defensive_rebounds_std_last_5,
  ROUND(STDDEV_POP(defensive_rebounds) OVER w10, 2) AS defensive_rebounds_std_last_10,
  ROUND(STDDEV_POP(dreb_pct) OVER w5, 2) AS  dreb_pct_std_last_5,
  ROUND(STDDEV_POP(dreb_pct) OVER w10, 2) AS dreb_pct_std_last_10,
  ROUND(STDDEV_POP(oreb_pct) OVER w5, 2) AS  oreb_pct_std_last_5,
  ROUND(STDDEV_POP(oreb_pct) OVER w10, 2) AS oreb_pct_std_last_10,
  ROUND(STDDEV_POP(steals) OVER w5, 2) AS  steals_std_last_5,
  ROUND(STDDEV_POP(steals) OVER w10, 2) AS steals_std_last_10,
  ROUND(STDDEV_POP(blocks) OVER w5, 2) AS  blocks_std_last_5,
  ROUND(STDDEV_POP(blocks) OVER w10, 2) AS blocks_std_last_10,
  ROUND(STDDEV_POP(assists) OVER w5, 2) AS  assists_std_last_5,
  ROUND(STDDEV_POP(assists) OVER w10, 2) AS assists_std_last_10,
  ROUND(STDDEV_POP(turnovers) OVER w5, 2) AS  turnovers_std_last_5,
  ROUND(STDDEV_POP(turnovers) OVER w10, 2) AS turnovers_std_last_10,
  ROUND(STDDEV_POP(team_turnovers) OVER w5, 2) AS  team_turnovers_std_last_5,
  ROUND(STDDEV_POP(team_turnovers) OVER w10, 2) AS team_turnovers_std_last_10,
  ROUND(STDDEV_POP(points_off_turnovers) OVER w5, 2) AS  points_off_turnovers_std_last_5,
  ROUND(STDDEV_POP(points_off_turnovers) OVER w10, 2) AS points_off_turnovers_std_last_10,
  ROUND(STDDEV_POP(assists_turnover_ratio) OVER w5, 2) AS  assists_turnover_ratio_std_last_5,
  ROUND(STDDEV_POP(assists_turnover_ratio) OVER w10, 2) AS assists_turnover_ratio_std_last_10,
  ROUND(STDDEV_POP(ast_fgm_pct) OVER w5, 2) AS  ast_fgm_pct_std_last_5,
  ROUND(STDDEV_POP(ast_fgm_pct) OVER w10, 2) AS ast_fgm_pct_std_last_10,
  ROUND(STDDEV_POP(personal_fouls) OVER w5, 2) AS  personal_fouls_std_last_5,
  ROUND(STDDEV_POP(personal_fouls) OVER w10, 2) AS personal_fouls_std_last_10,
  ROUND(STDDEV_POP(flagrant_fouls) OVER w5, 2) AS  flagrant_fouls_std_last_5,
  ROUND(STDDEV_POP(flagrant_fouls) OVER w10, 2) AS flagrant_fouls_std_last_10,
  ROUND(STDDEV_POP(player_tech_fouls) OVER w5, 2) AS  player_tech_fouls_std_last_5,
  ROUND(STDDEV_POP(player_tech_fouls) OVER w10, 2) AS player_tech_fouls_std_last_10,
  ROUND(STDDEV_POP(team_tech_fouls) OVER w5, 2) AS  team_tech_fouls_std_last_5,
  ROUND(STDDEV_POP(team_tech_fouls) OVER w10, 2) AS team_tech_fouls_std_last_10,
  ROUND(STDDEV_POP(coach_tech_fouls) OVER w5, 2) AS  coach_tech_fouls_std_last_5,
  ROUND(STDDEV_POP(coach_tech_fouls) OVER w10, 2) AS coach_tech_fouls_std_last_10,
  ROUND(STDDEV_POP(ejections) OVER w5, 2) AS  ejections_std_last_5,
  ROUND(STDDEV_POP(ejections) OVER w10, 2) AS ejections_std_last_10,
  ROUND(STDDEV_POP(foulouts) OVER w5, 2) AS  foulouts_std_last_5,
  ROUND(STDDEV_POP(foulouts) OVER w10, 2) AS foulouts_std_last_10,
  ROUND(STDDEV_POP(score_delta) OVER w5, 2) AS  score_delta_std_last_5,
  ROUND(STDDEV_POP(score_delta) OVER w10, 2) AS score_delta_std_last_10,
  ROUND(STDDEV_POP(possessions) OVER w5, 2) AS  possessions_std_last_5,
  ROUND(STDDEV_POP(possessions) OVER w10, 2) AS possessions_std_last_10,
  ROUND(STDDEV_POP(opp_points) OVER w5, 2) AS  opp_points_std_last_5,
  ROUND(STDDEV_POP(opp_points) OVER w10, 2) AS  opp_points_std_last_10,
  ROUND(STDDEV_POP(opp_fast_break_pts) OVER w5, 2) AS  opp_fast_break_pts_std_last_5,
  ROUND(STDDEV_POP(opp_fast_break_pts) OVER w10, 2) AS opp_fast_break_pts_std_last_10,
  ROUND(STDDEV_POP(opp_second_chance_pts) OVER w5, 2) AS  opp_second_chance_pts_std_last_5,
  ROUND(STDDEV_POP(opp_second_chance_pts) OVER w10, 2) AS opp_second_chance_pts_std_last_10,
  ROUND(STDDEV_POP(opp_field_goals_made) OVER w5, 2) AS  opp_field_goals_made_std_last_5,
  ROUND(STDDEV_POP(opp_field_goals_made) OVER w10, 2) AS opp_field_goals_made_std_last_10,
  ROUND(STDDEV_POP(opp_field_goals_att) OVER w5, 2) AS  opp_field_goals_att_std_last_5,
  ROUND(STDDEV_POP(opp_field_goals_att) OVER w10, 2) AS opp_field_goals_att_std_last_10,
  ROUND(STDDEV_POP(opp_field_goals_pct) OVER w5, 2) AS  opp_field_goals_pct_std_last_5,
  ROUND(STDDEV_POP(opp_field_goals_pct) OVER w10, 2) AS opp_field_goals_pct_std_last_10,
  ROUND(STDDEV_POP(opp_three_points_made) OVER w5, 2) AS  opp_three_points_made_std_last_5,
  ROUND(STDDEV_POP(opp_three_points_made) OVER w10, 2) AS opp_three_points_made_std_last_10,
  ROUND(STDDEV_POP(opp_three_points_att) OVER w5, 2) AS  opp_three_points_att_std_last_5,
  ROUND(STDDEV_POP(opp_three_points_att) OVER w10, 2) AS opp_three_points_att_std_last_10,
  ROUND(STDDEV_POP(opp_three_points_pct) OVER w5, 2) AS  opp_three_points_pct_std_last_5,
  ROUND(STDDEV_POP(opp_three_points_pct) OVER w10, 2) AS opp_three_points_pct_std_last_10,
  ROUND(STDDEV_POP(opp_two_points_made) OVER w5, 2) AS  opp_two_points_made_std_last_5,
  ROUND(STDDEV_POP(opp_two_points_made) OVER w10, 2) AS opp_two_points_made_std_last_10,
  ROUND(STDDEV_POP(opp_two_points_att) OVER w5, 2) AS  opp_two_points_att_std_last_5,
  ROUND(STDDEV_POP(opp_two_points_att) OVER w10, 2) AS opp_two_points_att_std_last_10,
  ROUND(STDDEV_POP(opp_two_points_pct) OVER w5, 2) AS  opp_two_points_pct_std_last_5,
  ROUND(STDDEV_POP(opp_two_points_pct) OVER w10, 2) AS opp_two_points_pct_std_last_10,
  ROUND(STDDEV_POP(opp_free_throws_made) OVER w5, 2) AS  opp_free_throws_made_std_last_5,
  ROUND(STDDEV_POP(opp_free_throws_made) OVER w10, 2) AS opp_free_throws_made_std_last_10,
  ROUND(STDDEV_POP(opp_free_throws_att) OVER w5, 2) AS  opp_free_throws_att_std_last_5,
  ROUND(STDDEV_POP(opp_free_throws_att) OVER w10, 2) AS opp_free_throws_att_std_last_10,
  ROUND(STDDEV_POP(opp_free_throws_pct) OVER w5, 2) AS  opp_free_throws_pct_std_last_5,
  ROUND(STDDEV_POP(opp_free_throws_pct) OVER w10, 2) AS opp_free_throws_pct_std_last_10,
  ROUND(STDDEV_POP(opp_ts_pct) OVER w5, 2) AS  opp_ts_pct_std_last_5,
  ROUND(STDDEV_POP(opp_ts_pct) OVER w10, 2) AS opp_ts_pct_std_last_10,
  ROUND(STDDEV_POP(opp_efg_pct) OVER w5, 2) AS  opp_efg_pct_std_last_5,
  ROUND(STDDEV_POP(opp_efg_pct) OVER w10, 2) AS opp_efg_pct_std_last_10,
  ROUND(STDDEV_POP(opp_rebounds) OVER w5, 2) AS  opp_rebounds_std_last_5,
  ROUND(STDDEV_POP(opp_rebounds) OVER w10, 2) AS opp_rebounds_std_last_10,
  ROUND(STDDEV_POP(opp_offensive_rebounds) OVER w5, 2) AS  opp_offensive_rebounds_std_last_5,
  ROUND(STDDEV_POP(opp_offensive_rebounds) OVER w10, 2) AS opp_offensive_rebounds_std_last_10,
  ROUND(STDDEV_POP(opp_defensive_rebounds) OVER w5, 2) AS  opp_defensive_rebounds_std_last_5,
  ROUND(STDDEV_POP(opp_defensive_rebounds) OVER w10, 2) AS opp_defensive_rebounds_std_last_10,
  ROUND(STDDEV_POP(opp_dreb_pct) OVER w5, 2) AS  opp_dreb_pct_std_last_5,
  ROUND(STDDEV_POP(opp_dreb_pct) OVER w10, 2) AS opp_dreb_pct_std_last_10,
  ROUND(STDDEV_POP(opp_oreb_pct) OVER w5, 2) AS  opp_oreb_pct_std_last_5,
  ROUND(STDDEV_POP(opp_oreb_pct) OVER w10, 2) AS opp_oreb_pct_std_last_10,
  ROUND(STDDEV_POP(opp_steals) OVER w5, 2) AS  opp_steals_std_last_5,
  ROUND(STDDEV_POP(opp_steals) OVER w10, 2) AS opp_steals_std_last_10,
  ROUND(STDDEV_POP(opp_blocks) OVER w5, 2) AS  opp_blocks_std_last_5,
  ROUND(STDDEV_POP(opp_blocks) OVER w10, 2) AS opp_blocks_std_last_10,
  ROUND(STDDEV_POP(opp_assists) OVER w5, 2) AS  opp_assists_std_last_5,
  ROUND(STDDEV_POP(opp_assists) OVER w10, 2) AS opp_assists_std_last_10,
  ROUND(STDDEV_POP(opp_turnovers) OVER w5, 2) AS  opp_turnovers_std_last_5,
  ROUND(STDDEV_POP(opp_turnovers) OVER w10, 2) AS opp_turnovers_std_last_10,
  ROUND(STDDEV_POP(opp_team_turnovers) OVER w5, 2) AS  opp_team_turnovers_std_last_5,
  ROUND(STDDEV_POP(opp_team_turnovers) OVER w10, 2) AS opp_team_turnovers_std_last_10,
  ROUND(STDDEV_POP(opp_points_off_turnovers) OVER w5, 2) AS  opp_points_off_turnovers_std_last_5,
  ROUND(STDDEV_POP(opp_points_off_turnovers) OVER w10, 2) AS opp_points_off_turnovers_std_last_10,
  ROUND(STDDEV_POP(opp_assists_turnover_ratio) OVER w5, 2) AS  opp_assists_turnover_ratio_std_last_5,
  ROUND(STDDEV_POP(opp_assists_turnover_ratio) OVER w10, 2) AS opp_assists_turnover_ratio_std_last_10,
  ROUND(STDDEV_POP(opp_ast_fgm_pct) OVER w5, 2) AS  opp_ast_fgm_pct_std_last_5,
  ROUND(STDDEV_POP(opp_ast_fgm_pct) OVER w10, 2) AS opp_ast_fgm_pct_std_last_10,
  ROUND(STDDEV_POP(opp_personal_fouls) OVER w5, 2) AS  opp_personal_fouls_std_last_5,
  ROUND(STDDEV_POP(opp_personal_fouls) OVER w10, 2) AS opp_personal_fouls_std_last_10,
  ROUND(STDDEV_POP(opp_flagrant_fouls) OVER w5, 2) AS  opp_flagrant_fouls_std_last_5,
  ROUND(STDDEV_POP(opp_flagrant_fouls) OVER w10, 2) AS opp_flagrant_fouls_std_last_10,
  ROUND(STDDEV_POP(opp_player_tech_fouls) OVER w5, 2) AS  opp_player_tech_fouls_std_last_5,
  ROUND(STDDEV_POP(opp_player_tech_fouls) OVER w10, 2) AS opp_player_tech_fouls_std_last_10,
  ROUND(STDDEV_POP(opp_team_tech_fouls) OVER w5, 2) AS  opp_team_tech_fouls_std_last_5,
  ROUND(STDDEV_POP(opp_team_tech_fouls) OVER w10, 2) AS opp_team_tech_fouls_std_last_10,
  ROUND(STDDEV_POP(opp_coach_tech_fouls) OVER w5, 2) AS  opp_coach_tech_fouls_std_last_5,
  ROUND(STDDEV_POP(opp_coach_tech_fouls) OVER w10, 2) AS opp_coach_tech_fouls_std_last_10,
  ROUND(STDDEV_POP(opp_ejections) OVER w5, 2) AS  opp_ejections_std_last_5,
  ROUND(STDDEV_POP(opp_ejections) OVER w10, 2) AS opp_ejections_std_last_10,
  ROUND(STDDEV_POP(opp_foulouts) OVER w5, 2) AS  opp_foulouts_std_last_5,
  ROUND(STDDEV_POP(opp_foulouts) OVER w10, 2) AS opp_foulouts_std_last_10,
  ROUND(STDDEV_POP(opp_score_delta) OVER w5, 2) AS  opp_score_delta_std_last_5,
  ROUND(STDDEV_POP(opp_score_delta) OVER w10, 2) AS opp_score_delta_std_last_10,
  ROUND(STDDEV_POP(opp_possessions) OVER w5, 2) AS  opp_possessions_std_last_5,
  ROUND(STDDEV_POP(opp_possessions) OVER w10, 2) AS opp_possessions_std_last_10
FROM (
  SELECT
    *,
    IF(win = true, 1, 0) as is_win,
    IF(win = false, 0, 1) as is_loss,
    ROUND(field_goals_att - offensive_rebounds + turnovers + 0.475 * free_throws_att, 2) as possessions,
    ROUND(opp_field_goals_att - opp_offensive_rebounds + opp_turnovers + 0.475 * opp_free_throws_att, 2) as opp_possessions,
    ROUND(points / (2 * (field_goals_att) + 0.475 * free_throws_att), 3) AS ts_pct,
    ROUND(opp_points / (2 * (opp_field_goals_att) + 0.475 * opp_free_throws_att), 3) AS opp_ts_pct,
    ROUND(((field_goals_made) + 0.5 * three_points_att) / (field_goals_att), 3) AS efg_pct,
    ROUND(((opp_field_goals_made) + 0.5 * opp_three_points_att) / (opp_field_goals_att), 3) AS opp_efg_pct,
    ROUND(defensive_rebounds / rebounds, 3) AS dreb_pct,
    ROUND(opp_defensive_rebounds / opp_rebounds, 3) AS opp_dreb_pct,
    ROUND(offensive_rebounds / rebounds, 3) AS oreb_pct,
    ROUND(opp_offensive_rebounds / opp_rebounds, 3) AS opp_oreb_pct,
    ROUND(assists / field_goals_made, 3) AS ast_fgm_pct,
    ROUND(opp_assists / opp_field_goals_made, 3) AS opp_ast_fgm_pct,
    (points_game - opp_points_game) as score_delta,
    (opp_points_game - points_game) as opp_score_delta
  FROM
    `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr`
  WHERE field_goals_att > 0  AND opp_field_goals_att > 0 AND rebounds > 0 AND opp_rebounds > 0 AND field_goals_made > 0  AND opp_field_goals_made > 0
    AND division_name = "NCAA Division I"
    AND opp_division_name = "NCAA Division I"
    AND scheduled_date > '2009-05-01'
)
WINDOW w1 AS (partition by season, team_id order by scheduled_date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
       w5 AS (partition by season, team_id order by scheduled_date ASC ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING),
       w10 AS (partition by season, team_id order by scheduled_date ASC ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING)
ORDER BY season, team_id