Rolling Ticket Sales Query | Dataford Interview Questions

MLB wants a daily view of ticket sales for each stadium, including dates when a stadium has no game. Write a PostgreSQL query for April 1 through April 3, 2024.

Requirements

Generate every date and stadium combination in the reporting range.
Include only ticket sales from completed MLB games.
Treat missing sales as zero, then calculate each stadium's rolling seven-calendar-day average. Because the date spine is complete, the window must include zero-sales dates.
Return stadium name, date, daily ticket sales, and the rolling average, rounded to two decimals.
Order by stadium name and report date.

Schema

stadiums

Column	Type	Description
stadium_idPK	INT	Unique stadium identifier
stadium_name	VARCHAR(100)	MLB stadium name
city	VARCHAR(100)	City where the stadium is located

games

Column	Type	Description
game_idPK	INT	Unique game identifier
stadium_id	INT	Stadium hosting the game
game_date	DATE	Scheduled game date
status	VARCHAR(20)	Game status, such as Completed or Postponed

ticket_sales

Column	Type	Description
sale_idPK	INT	Unique ticket-sale record identifier
game_id	INT	Game associated with the sale
tickets_sold	INT	Number of tickets in the sale record

Your question is Rolling Ticket Sales Query. Start with the requirements and the three 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