How far the S&P 500 tracker moved open to close: sessions by size band, July 2025 to June 2026

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMin(open, window_start) AS session_open,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) BETWEEN toDate('2025-07-01') AND 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 session_date
),
moves AS (
    SELECT round(abs(session_close - session_open) / session_open * 100, 3) AS abs_move_pct
    FROM daily
    WHERE session_open > 0
)
SELECT multiIf(abs_move_pct < 0.25, 'under 0.25%',
               abs_move_pct < 0.5, '0.25% to 0.5%',
               abs_move_pct < 1, '0.5% to 1%',
               abs_move_pct < 2, '1% to 2%',
               '2% and up') AS move_band,
       count() AS session_count,
       round(100 * count() / sum(count()) OVER (), 1) AS share_pct
FROM moves
GROUP BY move_band
ORDER BY min(abs_move_pct)