A decade of SPY daily closing moves, sorted into size buckets

WITH spy AS
(
    SELECT
        date,
        toFloat64(close) AS close_px,
        lagInFrame(toFloat64(close)) OVER
            (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2016-08-01'
      AND date <  '2026-08-01'
)
SELECT
    multiIf(move_pct < 0.5, 'under 0.5%',
            move_pct < 1.0, '0.5 to 1%',
            move_pct < 1.5, '1 to 1.5%',
            move_pct < 2.0, '1.5 to 2%',
            move_pct < 3.0, '2 to 3%',
                            '3% or more')                 AS move_bucket,
    count()                                               AS sessions,
    round(100 * count() / (SELECT count() FROM spy WHERE prev_close > 0), 1) AS share_pct
FROM
(
    SELECT abs(close_px / prev_close - 1) * 100 AS move_pct
    FROM spy
    WHERE prev_close > 0
)
GROUP BY move_bucket
ORDER BY min(move_pct)