Show a symbol's recent price trend with its 7-day and 30-day forecast overlaid (edit the symbol)

The query

WITH hist AS (
  SELECT to_timestamp(tp.datetime)::date AS date, tp.price AS actual_price
  FROM ticketprice tp
  JOIN ticketname tn ON tn.id = tp.ticketnameid
  WHERE tn.ticket = 'AAPL'
  ORDER BY tp.datetime DESC
  LIMIT 60
),
fc AS (
  SELECT
    (ta.computed_at + (substring(ta.analysis_type FROM 'forecast_(\d+)d')::int || ' days')::interval)::date AS date,
    MAX(CASE WHEN ta.analysis_type = 'forecast_7d' THEN (ta.payload->>'price')::float END) AS forecast_7d_price,
    MAX(CASE WHEN ta.analysis_type = 'forecast_30d' THEN (ta.payload->>'price')::float END) AS forecast_30d_price
  FROM ticker_analysis ta
  WHERE ta.ticket = 'AAPL' AND ta.analysis_type IN ('forecast_7d','forecast_30d')
  GROUP BY 1
)
SELECT date, actual_price, NULL::float AS forecast_7d_price, NULL::float AS forecast_30d_price FROM hist
UNION ALL
SELECT date, NULL::float, forecast_7d_price, forecast_30d_price FROM fc
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

• Get the 7-day forecast price for a specific stock (edit the symbol)
• Get the 3-day forecast % change for a specific crypto (edit the symbol)
• Get the 30-day forecast price for a specific fund (edit the symbol)
• Top 10 stocks by 7-day forecasted % gain, market-wide