2,173 answered market questions

WITH sent AS (
    SELECT toDate(published_utc) AS d,
           (countIf(s='positive') - countIf(s='negative')) / (countIf(s='positive') + countIf(s='negative')) AS net
    FROM (SELECT published_utc, JSONExtractString(ins,'sentiment') AS s
          FROM global_markets.stocks_news ARRAY JOIN insights AS ins
          WHERE JSONExtractString(ins,'sentiment') != '')
    GROUP BY d HAVING countIf(s='positive') + countIf(s='negative') >= 20
),
spyd AS (
    SELECT toDate(toTimeZone(window_start,'America/New_York')) AS d,
           argMax(toFloat64(close), toTimeZone(window_start,'America/New_York')) AS c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker='SPY' AND window_start >= '2024-01-01'
      AND (toHour(toTimeZone(window_start,'America/New_York'))*60 + toMinute(toTimeZone(window_start,'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
spy AS (
    SELECT d, c, leadInFrame(c,5) OVER w AS c5, leadInFrame(c,20) OVER w AS c20, leadInFrame(c,60) OVER w AS c60
    FROM spyd WINDOW w AS (ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 60 FOLLOWING)
),
j AS (
    SELECT s.net AS net, (spy.c5/spy.c-1)*100 AS f5, (spy.c20/spy.c-1)*100 AS f20, (spy.c60/spy.c-1)*100 AS f60
    FROM sent s INNER JOIN spy ON s.d = spy.d WHERE spy.c60 > 0
),
qt AS (SELECT quantile(0.1)(net) AS p10, quantile(0.9)(net) AS p90 FROM j)
SELECT multiIf(net <= (SELECT p10 FROM qt), 'Extreme fear (least upbeat)',
               net >= (SELECT p90 FROM qt), 'Extreme greed (most upbeat)',
               'In between') AS market_mood,
       count() AS days,
       round(avg(f5),2)  AS fwd_5d_pct,
       round(avg(f20),2) AS fwd_20d_pct,
       round(avg(f60),2) AS fwd_60d_pct
FROM j GROUP BY market_mood
ORDER BY multiIf(market_mood='Extreme fear (least upbeat)',0, market_mood='In between',1, 2)