Rolling 30-Day Asset Average | Dataford Interview Questions

BlackRock's Aladdin risk analytics workflow needs a time-series view of recent asset performance. Given daily closing prices and an in-scope asset set, write a PostgreSQL query that calculates each asset's daily return and rolling 30-calendar-day average return.

Requirements

Restrict the analysis to assets in portfolio BLK_CORE where is_in_scope = TRUE.
Calculate each daily return as the current closing price divided by the previous available closing price minus one, using LAG partitioned by asset.
Calculate the rolling average over the current date and preceding 29 calendar days, not merely the preceding 30 rows.
Preserve the first price observation for each asset, returning NULL for its daily return and rolling average.
Return results ordered by ticker and price date, with returns rounded to six decimal places.

Schema

assets

Column	Type	Description
asset_idPK	INT	Unique asset identifier
ticker	VARCHAR(12)	Trading symbol
asset_name	VARCHAR(100)	Asset name
portfolio_code	VARCHAR(30)	Portfolio membership code
is_in_scope	BOOLEAN	Whether the asset is included in the analysis

asset_prices

Column	Type	Description
price_idPK	INT	Unique price observation identifier
asset_id	INT	References assets.asset_id
price_date	DATE	Closing-price date
close_price	NUMERIC(12,4)	Closing price

Your question is Rolling 30-Day Asset 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