What the next ten sessions did, squeeze against no squeeze

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
px AS
(
    SELECT
        ticker,
        date,
        toFloat64(any(close)) AS c,
        toFloat64(any(high))  AS h,
        toFloat64(any(low))   AS l
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'JNJ')
      AND date >= '2023-07-01'
      AND date <  '2026-09-01'
    GROUP BY ticker, date
),
tr AS
(
    SELECT
        ticker,
        date,
        c,
        h,
        l,
        lagInFrame(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_c
    FROM px
),
stat AS
(
    SELECT
        ticker,
        date,
        c,
        stddevPop(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sd,
        count()      OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS bars,
        avg(if(prev_c > 0, greatest(h - l, abs(h - prev_c), abs(l - prev_c)), h - l))
                     OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS atr
    FROM tr
),
fwd AS
(
    SELECT
        date,
        c,
        bars,
        if(4 * sd < 3 * atr, 'In a squeeze', 'No squeeze') AS label,
        leadInFrame(c, 10) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) AS c_fwd
    FROM stat
)
SELECT
    label,
    count()                                      AS observation_count,
    round(100 * countIf(c_fwd > c) / count(), 1) AS up_share_pct,
    round(100 * avg(abs(c_fwd / c - 1)), 2)      AS avg_abs_move_pct
FROM fwd
WHERE bars = 20
  AND date >= '2023-09-01'
  AND c_fwd > 0
GROUP BY label
ORDER BY label