Calculations › Market Discovery

Show a sector overview across the market (count, average price, average YTD change)

The query

-- table market-wide: sector overview (count, avg price, avg YTD change)
WITH latest AS (
  SELECT DISTINCT ON (tp.ticketnameid) tp.ticketnameid, tp.price AS price_now
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock'
  ORDER BY tp.ticketnameid, tp.datetime DESC
),
year_start AS (
  SELECT DISTINCT ON (tp.ticketnameid) tp.ticketnameid, tp.price AS price_start
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock'
    AND tp.datetime >= EXTRACT(EPOCH FROM DATE_TRUNC('year', NOW()))::bigint
  ORDER BY tp.ticketnameid, tp.datetime ASC
)
SELECT sm.sector, COUNT(*) AS n, AVG(l.price_now) AS avg_price,
       AVG((l.price_now - y.price_start) / NULLIF(y.price_start,0) * 100) AS avg_ytd_pct_change
FROM latest l
JOIN year_start y ON y.ticketnameid = l.ticketnameid
JOIN symbolmetadata sm ON sm.ticketnameid = l.ticketnameid
WHERE sm.sector IS NOT NULL AND sm.sector != '' AND y.price_start >= 0.5
GROUP BY sm.sector
ORDER BY avg_ytd_pct_change DESC;

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

SectorCountAvg priceAvg YTD % change
Consumer Defensive 21 111.31 39.78
Energy 15 147.64 36.23
Utilities 6 14.93 19.05
Technology 68 140.05 18.37
Basic Materials 22 139.95 7.93
Communication Services 30 121.71 7.19
Industrials 108 168.53 4.89
Financial Services 57 191.96 1.51
Healthcare 63 137.26 -0.66
Consumer Cyclical 50 147.19 -8.73
Real Estate 32 79.10 -13.87

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