breaks_by_year

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
        date,
        day_high,
        day_close,
        max(day_high) OVER (ORDER BY date ASC ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS prior_high
    FROM bars
),
yearly AS
(
    SELECT
        toString(toYear(date))                                    AS year,
        count()                                                   AS sessions,
        countIf(day_high > prior_high)                            AS cleared_prior_high,
        countIf(day_high > prior_high AND day_close < prior_high)  AS closed_back_below
    FROM flagged
    WHERE prior_high > 0
    GROUP BY year
)
SELECT
    year,
    sessions,
    cleared_prior_high,
    closed_back_below,
    round(100 * closed_back_below / cleared_prior_high, 1)                           AS rejection_rate_pct,
    round(100 * sum(closed_back_below) OVER () / sum(cleared_prior_high) OVER (), 1) AS period_avg_rejection_pct
FROM yearly
ORDER BY year