CREATE VIEW sports_basketball_game_stats ASSELECT -- Game summary information (who played, time, etc) game.id AS game_id, game.game_date AS game_date, game.start_time AS start_time, game.end_time AS end_time, game.audio_url AS audio_url, game.story_url AS story_url, game.video_url AS video_url, game.photo_url AS photo_url, home_team.name AS home_team_name, home_team.abbr AS home_team_abbr, away_team.name AS away_team_name, away_team.abbr AS away_team_abbr, -- Does the game have quarterly scores available? (home_stats.score_q1 IS NOT NULL) AND (home_stats.score_q2 IS NOT NULL) AND (home_stats.score_q3 IS NOT NULL) AND (home_stats.score_q4 IS NOT NULL) AND (away_stats.score_q1 IS NOT NULL) AND (away_stats.score_q2 IS NOT NULL) AND (away_stats.score_q3 IS NOT NULL) AND (away_stats.score_q4 IS NOT NULL) AS has_quarterly_scores, -- Scores: home team home_stats.score_q1 AS home_score_q1, home_stats.score_q2 AS home_score_q2, home_stats.score_q3 AS home_score_q3, home_stats.score_q4 AS home_score_q4, home_stats.score_ot AS home_score_ot, home_stats.score_2ot AS home_score_2ot, COALESCE(home_stats.score_q1, 0) + COALESCE(home_stats.score_q2, 0) + COALESCE(home_stats.score_q3, 0) + COALESCE(home_stats.score_q4, 0) + COALESCE(home_stats.score_ot, 0) + COALESCE(home_stats.score_2ot, 0) AS home_team_score, -- Scores: away team away_stats.score_q1 AS away_score_q1, away_stats.score_q2 AS away_score_q2, away_stats.score_q3 AS away_score_q3, away_stats.score_q4 AS away_score_q4, away_stats.score_ot AS away_score_ot, away_stats.score_2ot AS away_score_2ot, COALESCE(away_stats.score_q1, 0) + COALESCE(away_stats.score_q2, 0) + COALESCE(away_stats.score_q3, 0) + COALESCE(away_stats.score_q4, 0) + COALESCE(away_stats.score_ot, 0) + COALESCE(away_stats.score_2ot, 0) AS away_team_score, -- Home team stats home_stats.field_goals_attempted AS home_field_goals_attempted, home_stats.field_goals_made AS home_field_goals_made, home_stats.three_pointers_attempted AS home_three_pointers_attempted, home_stats.three_pointers_made AS home_three_pointers_made, home_stats.free_throws_attempted AS home_free_throws_attempted, home_stats.free_throws_made AS home_free_throws_made, home_stats.fouls AS home_fouls, home_stats.turnovers AS home_turnovers, home_stats.assists AS home_assists, home_stats.blocked_shots AS home_blocked_shots, home_stats.steals AS home_steals, home_stats.offensive_rebounds AS home_offensive_rebounds, home_stats.defensive_rebounds AS home_defensive_rebounds, home_stats.offensive_rebounds + home_stats.defensive_rebounds AS home_total_rebounds, -- Home team averages CASE WHEN home_stats.field_goals_attempted > 0 THEN home_stats.field_goals_made::FLOAT / home_stats.field_goals_attempted ELSE 0 END AS home_field_goal_average, CASE WHEN home_stats.three_pointers_attempted > 0 THEN home_stats.three_pointers_made::FLOAT / home_stats.three_pointers_attempted ELSE 0 END AS home_three_point_average, CASE WHEN home_stats.free_throws_attempted > 0 THEN home_stats.free_throws_made::FLOAT / home_stats.free_throws_attempted ELSE 0 END AS home_free_throw_average, CASE WHEN home_stats.turnovers > 0 THEN home_stats.assists::FLOAT / home_stats.turnovers ELSE 0 END AS home_assist_turnover_ratio, -- Away team stats away_stats.field_goals_attempted AS away_field_goals_attempted, away_stats.field_goals_made AS away_field_goals_made, away_stats.three_pointers_attempted AS away_three_pointers_attempted, away_stats.three_pointers_made AS away_three_pointers_made, away_stats.free_throws_attempted AS away_free_throws_attempted, away_stats.free_throws_made AS away_free_throws_made, away_stats.fouls AS away_fouls, away_stats.turnovers AS away_turnovers, away_stats.assists AS away_assists, away_stats.blocked_shots AS away_blocked_shots, away_stats.steals AS away_steals, away_stats.offensive_rebounds AS away_offensive_rebounds, away_stats.defensive_rebounds AS away_defensive_rebounds, away_stats.offensive_rebounds + away_stats.defensive_rebounds AS away_total_rebounds, -- Away team averages CASE WHEN away_stats.field_goals_attempted > 0 THEN away_stats.field_goals_made::FLOAT / away_stats.field_goals_attempted ELSE 0 END AS away_field_goal_average, CASE WHEN away_stats.three_pointers_attempted > 0 THEN away_stats.three_pointers_made::FLOAT / away_stats.three_pointers_attempted ELSE 0 END AS away_three_point_average, CASE WHEN away_stats.free_throws_attempted > 0 THEN away_stats.free_throws_made::FLOAT / away_stats.free_throws_attempted ELSE 0 END AS away_free_throw_average, CASE WHEN away_stats.turnovers > 0 THEN away_stats.assists::FLOAT / away_stats.turnovers ELSE 0 END AS away_assist_turnover_ratioFROM sports_basketball_games AS gameJOIN sports_basketball_teams_in_games AS home_stats ON home_stats.game_id = game.id AND home_stats.is_home_team = TRUEJOIN sports_basketball_teams_in_games AS away_stats ON away_stats.game_id = game.id AND away_stats.is_home_team != TRUEJOIN sports_basketball_teams AS home_team ON home_team.id = home_stats.team_idJOIN sports_basketball_teams AS away_team ON away_team.id = away_stats.team_id;