Show a 7-day moving average trend for the stock market benchmark, last 90 days

The query

-- graph market-wide: 7-day moving average trend for the stock market benchmark, last 90 days
WITH daily AS (
  SELECT to_timestamp(tp.datetime)::date AS date, AVG(tp.price) AS avg_price
  FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.tickettype = 'stock'
    AND tp.datetime >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '90 days'))::bigint
  GROUP BY 1
)
SELECT date, AVG(avg_price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily 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 the cumulative return (indexed to 100) across all stocks, 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