Rolling 30-Day Volatility Query | Dataford Interview Questions

Your question is Rolling 30-Day Volatility Query. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Fidelity Investments uses market data analytics to monitor risk for equities available through Fidelity Active Trader Pro. Write a PostgreSQL query that calculates rolling 30-day volatility for the equities in a specified watchlist.

Use daily closing prices to calculate simple daily returns. Treat the 30-day period as the preceding 30 calendar days, including the current observation. Return volatility only when at least two daily returns exist in the rolling window.

Requirements

Join the watchlist to the security master and daily prices.
Calculate each equity's daily return using LAG.
Calculate sample standard deviation of daily returns over the preceding 30 calendar days using a window function.
Return the ticker, price date, daily return, number of returns in the window, and volatility, ordered by ticker and date.

Schema

requested_equities

Column	Type	Description
tickerPK	VARCHAR(10)	Ticker included in the requested watchlist

equities

Column	Type	Description
tickerPK	VARCHAR(10)	Unique equity ticker
security_name	VARCHAR(100)	Equity security name
asset_class	VARCHAR(30)	Security asset classification

daily_prices

Column	Type	Description
tickerPK	VARCHAR(10)	Ticker for the price observation
price_datePK	DATE	Trading date
close_price	NUMERIC(12,4)	Unadjusted daily closing price