Rolling 7-Day Freight Average | Dataford Interview Questions

Write a SQL query using window functions to find the rolling average of freight shipping volume over a 7-day period at XPO.

Use the provided freight_shipments and calendar_dates tables. Include dates with no shipments as zero volume, and calculate each average using the current date and up to the six preceding calendar dates.

Output

One row per calendar date, with calendar_date, daily_volume, and rolling_7_day_avg.
Include all calendar dates in the provided range and order by calendar_date ascending.

Schema

freight_shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
shipment_date	DATE	Date on which freight was shipped
freight_volume	DECIMAL(12,2)	Freight volume for the shipment

calendar_dates

Column	Type	Description
calendar_datePK	DATE	Date included in the reporting calendar

Your question is Rolling 7-Day Freight Average. 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