SQL Rolling Averages Over 10 Games | Dataford Interview Questions

How would you use SQL window functions to compute rolling averages for player performance over the last 10 games?

Use the provided player and game-statistics tables. Calculate rolling averages for points, rebounds, and assists independently for each player, using the available games when fewer than 10 prior games exist. NULL points values should be ignored by the average.

Output

One row per matched player game with the player name, game date, game number, points, and three rolling averages.
Name the average columns rolling_10_game_avg_points, rolling_10_game_avg_rebounds, and rolling_10_game_avg_assists.
Order by player name, game date, and game number ascending.

Schema

players

Column	Type	Description
player_idPK	INT	Unique player identifier
player_name	VARCHAR(100)	Player's full name
position	VARCHAR(20)	Primary basketball position

game_stats

Column	Type	Description
game_idPK	INT	Unique game-statistics record identifier
player_id	INT	Identifier of the player associated with the statistics
game_date	DATE	Date of the game
game_number	INT	Player's chronological game number
points	NUMERIC(6,2)	Points scored in the game
rebounds	INT	Total rebounds in the game
assists	INT	Total assists in the game

Your question is SQL Rolling Averages Over 10 Games. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem