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.
