How often a resting limit was reached, by distance from the prior close (SPY, 2021 to Sep 2026)

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH daily AS
(
    SELECT
        date,
        toFloat64(any(close)) AS close,
        toFloat64(min(low))   AS low,
        toFloat64(max(high))  AS high
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2021-01-01'
      AND date <  '2026-10-01'
    GROUP BY date
),
framed AS
(
    SELECT
        date,
        low,
        high,
        lagInFrame(close, 1) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_close
    FROM daily
)
SELECT
    limit_distance,
    round(100 * countIf(low  <= prior_close * (1 - limit_distance / 100)) / count(), 1) AS buy_limit_touched_pct,
    round(100 * countIf(high >= prior_close * (1 + limit_distance / 100)) / count(), 1) AS sell_limit_touched_pct
FROM
(
    SELECT
        low,
        high,
        prior_close,
        arrayJoin([0.25, 0.5, 1.0, 1.5, 2.0, 3.0]) AS limit_distance
    FROM framed
    WHERE prior_close > 0
)
GROUP BY limit_distance
ORDER BY limit_distance