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	62	55.01
BZ=F	Brent Crude Oil Last Day Financial Futures	64	53.22
CL=F	Crude Oil Oct 26	64	50.37
KC=F	Coffee Dec 26	62	47.88
PA=F	Palladium Dec 26	64	42.78
NG=F	Natural Gas Oct 26	64	39.64
SI=F	Silver Dec 26	64	36.29
PL=F	Platinum Oct 26	64	35.57
ZW=F	Chicago SRW Wheat Futures,Dec-2	63	34.38
SB=F	Sugar #11 Oct 26	62	31.61
CT=F	Cotton Mar 26	62	30.05
ZC=F	Corn Futures,Dec-2026	63	25.78
GC=F	Gold Dec 26	64	22.06
HG=F	Copper Dec 26	64	21.45
ZS=F	Soybean Futures,Nov-2026	63	19.06

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.