How big a typical session is: SPY close-to-close moves by size band, calendar 2025

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(close, window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2025-01-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2025-12-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
paired AS (
    SELECT d, close_px,
           any(close_px) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_px
    FROM daily
),
rets AS (
    SELECT d, round(100 * (toFloat64(close_px) / toFloat64(prev_px) - 1), 3) AS ret_pct
    FROM paired
    WHERE prev_px > 0
)
SELECT multiIf(abs(ret_pct) < 0.25, 'under 0.25%',
               abs(ret_pct) < 0.5, '0.25 to 0.5%',
               abs(ret_pct) < 1.0, '0.5 to 1%',
               abs(ret_pct) < 2.0, '1 to 2%',
               '2% and up') AS move_bucket,
       count() AS sessions,
       round(100 * count() / sum(count()) OVER (), 1) AS pct_of_sessions
FROM rets
GROUP BY move_bucket
ORDER BY min(abs(ret_pct))