Rolling 5-Minute Price Average | Dataford Interview Questions

Write a query or script to calculate the rolling 5-minute average price of a stock asset, handling periods of inactivity where no trades occur for Jane Street’s data.

Use the assets and trades tables and calculate results for the asset with symbol AAPL. Include every five-minute timestamp from the asset observation start through its observation end. A missing average must be represented as NULL, not zero.

Output

One row per five-minute timestamp for AAPL
Columns: asset_symbol, bucket_start, and rolling_5_min_avg_price
Include trades from the preceding five minutes through the timestamp, inclusive, and ignore NULL prices
Order by bucket_start ascending

Schema

assets

Column	Type	Description
asset_idPK	INT	Unique asset identifier
symbol	VARCHAR(20)	Stock symbol
observation_start	TIMESTAMPTZ	Beginning of the reporting interval
observation_end	TIMESTAMPTZ	End of the reporting interval

trades

Column	Type	Description
trade_idPK	INT	Unique trade identifier
symbol	VARCHAR(20)	Traded stock symbol
trade_time	TIMESTAMPTZ	Timestamp when the trade occurred
price	NUMERIC(12,4)	Trade price

Your question is Rolling 5-Minute Price 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