-- Calculate the standard deviation of daily prices over the last 30 days to gauge the volatility of each cryptocurrency.
WITH DailyPrices AS (
SELECT
SYMBOL,
DATE(RECORDED_AT) AS Date,
AVG(PRICE) AS AvgDailyPrice
FROM osmosis.price.dim_prices
WHERE RECORDED_AT >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY SYMBOL, DATE(RECORDED_AT)
)
SELECT
SYMBOL,
STDDEV(AvgDailyPrice) AS PriceVolatility
FROM DailyPrices
GROUP BY SYMBOL
ORDER BY PriceVolatility DESC;