rejection_by_excess

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 bars AS
(
    SELECT
        date,
        max(toFloat64(high))  AS day_high,
        max(toFloat64(close)) AS day_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2015-01-01'
      AND date <  '2026-10-01'
    GROUP BY date
),
flagged AS
(
    SELECT
        day_high,
        day_close,
        max(day_high) OVER (ORDER BY date ASC ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS prior_high
    FROM bars
),
cleared AS
(
    SELECT
        round(100 * (day_high / prior_high - 1), 4) AS excess_pct,
        day_close < prior_high                      AS below_at_close
    FROM flagged
    WHERE prior_high > 0
      AND day_high > prior_high
)
SELECT
    multiIf(excess_pct < 0.10, '0.00-0.10%',
            excess_pct < 0.25, '0.10-0.25%',
            excess_pct < 0.50, '0.25-0.50%',
            excess_pct < 1.00, '0.50-1.00%',
                               'over 1.00%') AS excess_above_high,
    count()                                  AS sessions,
    countIf(below_at_close)                  AS closed_back_below,
    round(100 * countIf(below_at_close) / count(), 1) AS rejection_rate_pct
FROM cleared
GROUP BY excess_above_high
ORDER BY min(excess_pct)