SPY: touched versus finished above, 21-session forward windows since 2011

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,
            any(toFloat64(close)) AS c,
            max(toFloat64(high))  AS h
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2011-01-01'
          AND date <  '2026-07-01'
        GROUP BY date
    ),
    fwd AS (
        SELECT
            c AS spot,
            max(h) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 21 FOLLOWING)      AS fwd_max_high,
            leadInFrame(c, 21) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 21 FOLLOWING) AS close_fwd_21
        FROM bars
    ),
    trials AS (
        SELECT
            arrayJoin([1, 2, 3, 5])     AS pct_away,
            spot * (1 + pct_away / 100) AS level,
            fwd_max_high >= level       AS touched,
            close_fwd_21 >= level       AS finished_above
        FROM fwd
        WHERE close_fwd_21 > 0
    )
SELECT
    concat(toString(pct_away), '% above spot')   AS strike_distance,
    round(100 * avg(touched), 1)                 AS touch_pct,
    round(100 * avg(finished_above), 1)          AS finish_above_pct,
    round(avg(touched) / avg(finished_above), 2) AS touch_to_finish_ratio,
    count()                                      AS windows
FROM trials
GROUP BY pct_away
ORDER BY pct_away