3,256 answered market questions

WITH d AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS dt,
           argMax(toFloat64(close), toTimeZone(window_start, 'America/New_York')) AS c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('KO','SPY','NVDA')
      AND window_start >= now() - INTERVAL 800 DAY
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, dt
),
r AS (
    SELECT ticker, dt,
           c / lagInFrame(c) OVER (PARTITION BY ticker ORDER BY dt) - 1 AS ret
    FROM d
),
f AS (
    SELECT ticker, dt, ret FROM r WHERE ret IS NOT NULL AND ret > -0.5 AND ret < 0.5
),
cum AS (
    SELECT ticker, dt,
           100 * exp(sum(log(1 + ret)) OVER (PARTITION BY ticker ORDER BY dt)) AS idx
    FROM f
),
wk AS (
    SELECT ticker, toMonday(dt) AS week, argMax(idx, dt) AS wv
    FROM cum GROUP BY ticker, week
)
SELECT week,
       round(maxIf(wv, ticker = 'KO'), 1) AS ko_calm,
       round(maxIf(wv, ticker = 'SPY'), 1) AS spy_market,
       round(maxIf(wv, ticker = 'NVDA'), 1) AS nvda_wild
FROM wk
GROUP BY week
ORDER BY week