Show a rolling 30-day volatility trend for the stock market, last 180 days

The query

-- graph market-wide: rolling 30-day volatility trend for the stock market, last 180 days
WITH daily AS (
  SELECT to_timestamp(tp.datetime)::date AS date, AVG(tp.price) AS avg_price
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock'
    AND tp.datetime >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '180 days'))::bigint
  GROUP BY 1
),
daily_return AS (
  SELECT date, (avg_price - LAG(avg_price) OVER (ORDER BY date)) / NULLIF(LAG(avg_price) OVER (ORDER BY date),0) * 100 AS ret
  FROM daily
)
SELECT date, STDDEV(ret) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_volatility_30d
FROM daily_return
ORDER BY date;

Runs against Cryptobi's market tables — ticketname (symbols), ticketprice (daily prices and volume) and symbolmetadata (sector, exchange, market cap and other reference fields).

Current output

Live output, first 15 rows, refreshed periodically. Model estimates are trend projections from recent prices, not financial advice.

Related calculations

• Show the price trend for a symbol in the market over the last 30 days (edit the symbol)
• Show the average daily price trend across all stocks (market benchmark) over the last 90 days
• Show a 7-day moving average trend for the stock market benchmark, last 90 days
• Show the cumulative return (indexed to 100) across all stocks, last 90 days
• Show the average daily crypto market price trend, last 90 days
• Show the weekly average price trend for all funds, last 2 years