Show the average stock volatility by sector, last 30 days

The query

-- barchart market-wide: average volatility (stddev of daily % return, 30d) by sector, stocks
WITH recent AS (
  SELECT tp.ticketnameid, tp.datetime, tp.price,
         LAG(tp.price) OVER (PARTITION BY tp.ticketnameid ORDER BY tp.datetime) AS prev_price
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock'
    AND tp.datetime >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '30 days'))::bigint
),
per_symbol_vol AS (
  SELECT ticketnameid, STDDEV((price - prev_price) / NULLIF(prev_price,0) * 100) AS vol
  FROM recent WHERE prev_price IS NOT NULL
  GROUP BY ticketnameid
)
SELECT sm.sector, AVG(v.vol) AS avg_volatility, COUNT(*) AS n
FROM per_symbol_vol v
JOIN symbolmetadata sm ON sm.ticketnameid = v.ticketnameid
WHERE sm.sector IS NOT NULL AND sm.sector != ''
GROUP BY sm.sector
ORDER BY avg_volatility DESC;

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 11 rows, refreshed periodically. Model estimates are trend projections from recent prices, not financial advice.

Related calculations

• Show the top 10 most volatile stocks in the market, last 30 days
• Get the risk profile (max drawdown, volatility, Sharpe ratio) for a specific symbol (edit the symbol)
• How many stocks had an unusual volume anomaly in the last 30 days?