Calculations › Market Breakdown

Show the average 30-day % change by sector across all stocks

The query

-- barchart market-wide: average 30-day % change by sector (stocks only)
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 = 'stock'
  ORDER BY tp.ticketnameid, tp.datetime DESC
),
month_ago AS (
  SELECT DISTINCT ON (tp2.ticketnameid) tp2.ticketnameid, tp2.price AS price_1m
  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 sm.sector, AVG((l.price_now - m.price_1m) / NULLIF(m.price_1m,0) * 100) AS avg_pct_change_1m,
       COUNT(*) AS n
FROM latest l
JOIN month_ago m ON m.ticketnameid = l.ticketnameid
JOIN symbolmetadata sm ON sm.ticketnameid = l.ticketnameid
WHERE sm.sector IS NOT NULL AND sm.sector != ''
GROUP BY sm.sector
ORDER BY avg_pct_change_1m DESC
LIMIT 15;

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

SectorAvg 1m changeCount
Energy -0.48 15
Technology -0.77 73
Communication Services -1.54 34
Industrials -1.57 111
Utilities -2.53 6
Healthcare -2.78 72
Financial Services -3.04 59
Consumer Defensive -4.35 21
Real Estate -5.35 33
Consumer Cyclical -5.57 50
Basic Materials -6.50 22

Live output, first 11 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