Calculations › Market Discovery

Which materials and commodities moved the most in the last 30 days?

The query

-- table market-wide: biggest material moves over 30 days
WITH latest AS (
  SELECT DISTINCT ON (tp.ticketnameid) tp.ticketnameid, tp.price AS price_now, tp.datetime AS ts_now
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'material'
  ORDER BY tp.ticketnameid, tp.datetime DESC
),
earlier AS (
  SELECT DISTINCT ON (tp2.ticketnameid) tp2.ticketnameid, tp2.price AS price_then
  FROM ticketprice tp2 JOIN latest l ON l.ticketnameid = tp2.ticketnameid
  WHERE tp2.datetime <= l.ts_now - 30*86400
  ORDER BY tp2.ticketnameid, tp2.datetime DESC
)
SELECT tn.ticket AS symbol, COALESCE(NULLIF(sm.name, tn.ticket), tn.ticketname, tn.ticket) AS name,
       l.price_now, sm.currency,
       (l.price_now - e.price_then) / NULLIF(e.price_then,0) * 100 AS pct_change
FROM latest l
JOIN earlier e ON e.ticketnameid = l.ticketnameid
JOIN ticketname tn ON tn.id = l.ticketnameid
LEFT JOIN symbolmetadata sm ON sm.ticketnameid = l.ticketnameid
ORDER BY abs((l.price_now - e.price_then) / NULLIF(e.price_then,0)) DESC
LIMIT 25

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

SymbolNamePriceCurrency% change
LBS=F Random Length Lumber Futures 344 USD -17.96
CC=F Cocoa Dec 26 5448 USD -16.85
KC=F Coffee Dec 26 289.25 USX -15.49
CT=F Cotton Mar 26 77.26 USX -14.17
PA=F Palladium Dec 26 1188 USD -11.78
ZW=F Chicago SRW Wheat Futures,Dec-2 681.50 USX -9.71
BZ=F Brent Crude Oil Last Day Financial Futures 102.56 USD 7.25
SI=F Silver Dec 26 61.36 USD -5.20
GC=F Gold Dec 26 4205.60 USD -3.68
ZC=F Corn Futures,Dec-2026 500.75 USX -3.47
SB=F Sugar #11 Oct 26 18.96 USX 3.27
CL=F Crude Oil Oct 26 93.02 USD 2.21
ZS=F Soybean Futures,Nov-2026 1280.75 USX -1.61
PL=F Platinum Oct 26 1732.40 USD -1.56
HG=F Copper Dec 26 6.58 USD 1.17

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.

Sign Up Free

Related calculations