2,173 answered market questions

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMax(close, window_start) AS close_price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'VOO', 'IVV', 'SPLG', 'VTI', 'RSP')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2026-01-02')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session_date
),
with_prior AS (
    SELECT ticker,
           session_date,
           close_price,
           lagInFrame(close_price) OVER (PARTITION BY ticker ORDER BY session_date) AS prior_close
    FROM daily
),
moves AS (
    SELECT ticker, session_date, close_price / prior_close - 1 AS daily_move
    FROM with_prior
    WHERE prior_close > 0
),
benchmark AS (
    SELECT session_date, daily_move
    FROM moves
    WHERE ticker = 'SPY'
)
SELECT m.ticker AS ticker,
       round(avg(abs(m.daily_move - b.daily_move)) * 10000, 2) AS avg_daily_gap_bps,
       round(max(abs(m.daily_move - b.daily_move)) * 10000, 2) AS max_daily_gap_bps
FROM moves AS m
INNER JOIN benchmark AS b ON m.session_date = b.session_date
WHERE m.ticker != 'SPY'
GROUP BY m.ticker
ORDER BY avg_daily_gap_bps ASC