2,170 answered market questions

WITH
    daily AS
    (
        SELECT
            date,
            max(toFloat64(close)) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2011-01-01'
          AND date <  '2026-07-01'
        GROUP BY date
    ),
    spans AS
    (
        SELECT
            close_px,
            leadInFrame(close_px, 21)
                OVER (ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS close_fwd
        FROM daily
    )
SELECT
    concat(toString(threshold), '% or more')                                     AS move_size,
    round(100 * countIf(abs(100 * (close_fwd - close_px) / close_px) >= threshold)
        / count(), 1)                                                            AS share_of_windows_pct
FROM spans
ARRAY JOIN [2, 3, 4, 5, 6, 8, 10, 12] AS threshold
WHERE close_fwd > 0
GROUP BY threshold
ORDER BY threshold