Rolling Average with SQL Window Functions | Dataford Interview Questions

Write a SQL query using window functions to calculate a rolling average of commodity prices over the last 30 days.

Calculate the average separately for each commodity. Treat the 30-day period as inclusive of the observation date, and allow NULL prices to be ignored by the average.

Output

One row per price observation.
Columns: commodity, price_date, price, and rolling_avg_30d.
Include all observations and order by commodity, then price_date ascending.

Schema

commodity_prices

Column	Type	Description
price_idPK	INT	Unique price observation identifier
commodity	VARCHAR(50)	Commodity name
price_date	DATE	Date of the observed commodity price
price	NUMERIC(10,2)	Observed commodity price

Your question is Rolling Average with SQL Window Functions. 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