Which materials are the most volatile right now (90-day annualised)?

The query

-- table market-wide: annualised volatility of every material, most volatile first
WITH daily 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 = 'material'
    AND tp.datetime >= EXTRACT(EPOCH FROM NOW())::bigint - 90*86400
),
rets AS (
  SELECT ticketnameid, (price - prev_price) / NULLIF(prev_price,0) AS ret
  FROM daily WHERE prev_price IS NOT NULL
)
SELECT tn.ticket AS symbol, COALESCE(NULLIF(sm.name, tn.ticket), tn.ticketname, tn.ticket) AS name,
       COUNT(*) AS days_measured,
       STDDEV_SAMP(r.ret) * SQRT(252) * 100 AS annualised_volatility_pct
FROM rets r
JOIN ticketname tn ON tn.id = r.ticketnameid
LEFT JOIN symbolmetadata sm ON sm.ticketnameid = r.ticketnameid
GROUP BY tn.ticket, sm.name, tn.ticketname
HAVING COUNT(*) >= 20
ORDER BY annualised_volatility_pct DESC NULLS LAST

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

Symbol	Name	Days measured	Annualised volatility %
CC=F	Cocoa Dec 26	61	55.42
BZ=F	Brent Crude Oil Last Day Financial Futures	62	54.98
CL=F	Crude Oil Oct 26	62	50.92
KC=F	Coffee Dec 26	61	48.26
PA=F	Palladium Dec 26	62	43.44
NG=F	Natural Gas Oct 26	62	40.11
SI=F	Silver Dec 26	62	36.71
PL=F	Platinum Oct 26	62	36.32
ZW=F	Chicago SRW Wheat Futures,Dec-2	61	34.95
CT=F	Cotton Mar 26	61	31.62
SB=F	Sugar #11 Oct 26	61	29.36
ZC=F	Corn Futures,Dec-2026	61	26.13
GC=F	Gold Dec 26	62	22.42
HG=F	Copper Dec 26	62	21.96
ZS=F	Soybean Futures,Nov-2026	61	19.22

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

Run this on your own holdings, change it, or write your own in SQL or Python — then put it on a dashboard or have it emailed on a schedule. Free, no credit card.