Show the top 10 most volatile stocks in the market, last 30 days

The query

-- barchart market-wide: top 10 most volatile stocks (stddev of daily % return, 30d)
WITH recent AS (
  SELECT tp.ticketnameid, 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.price >= 1
    AND tp.datetime >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '30 days'))::bigint
)
SELECT tn.ticket AS symbol, STDDEV((r.price - r.prev_price) / NULLIF(r.prev_price,0) * 100) AS volatility
FROM recent r JOIN ticketname tn ON tn.id = r.ticketnameid
WHERE r.prev_price IS NOT NULL
GROUP BY tn.ticket
HAVING COUNT(*) >= 15
ORDER BY volatility DESC
LIMIT 10;

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

Related calculations

• Show the average stock volatility by sector, 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?