Rolling Average Flight Delays | Dataford Interview Questions

Write a SQL query to calculate the rolling average of flight delays by airport over a seven-day window.

Use the flight_delays table. Calculate the average across flights with non-null delay values, including the current date and preceding six calendar days.

Output

One row per airport and flight date present in the data.
Columns: airport_code, flight_date, and rolling_avg_delay.
Round the average to two decimal places and order by airport_code, then flight_date.

Schema

flight_delays

Column	Type	Description
flight_idPK	INT	Unique flight identifier
airport_code	VARCHAR(3)	Three-letter airport code
flight_date	DATE	Date of the flight
delay_minutes	INT	Arrival delay in minutes, where negative values indicate an early arrival

Your question is Rolling Average Flight Delays. Start with the requirements and the one table 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