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 110.05 37.96
Energy 15 148.79 36.68
Technology 68 140.86 19.08
Utilities 6 14.62 16.55
Communication Services 30 121.31 6.48
Basic Materials 22 137.69 6.36
Industrials 108 166.98 4.03
Financial Services 57 190.04 0.46
Healthcare 63 135.45 -1.40
Consumer Cyclical 50 146.06 -9.68
Real Estate 32 78.41 -14.65

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