Show the cumulative return (indexed to 100) across all stocks, last 90 days

The query

-- graph market-wide: cumulative return (indexed to 100) across all stocks, last 90 days
WITH windowed AS (
  SELECT tp.ticketnameid, to_timestamp(tp.datetime)::date AS date, tp.price
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock' AND tp.price >= 0.5
    AND tp.datetime >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '90 days'))::bigint
),
indexed AS (
  SELECT date, ticketnameid,
         100 * price / FIRST_VALUE(price) OVER (PARTITION BY ticketnameid ORDER BY date) AS idx_value
  FROM windowed
)
SELECT date, AVG(idx_value) AS market_index
FROM indexed GROUP BY date ORDER BY date;

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

Live output, first 15 rows, refreshed periodically. Model estimates are trend projections from recent prices, not financial advice.

Related calculations

• Show the price trend for a symbol in the market over the last 30 days (edit the symbol)
• Show the average daily price trend across all stocks (market benchmark) over the last 90 days
• Show a 7-day moving average trend for the stock market benchmark, last 90 days
• Show the average daily crypto market price trend, last 90 days
• Show the weekly average price trend for all funds, last 2 years
• Show a rolling 30-day volatility trend for the stock market, last 180 days