Sessions that gapped through a 2% stop, and whether the limit price traded

WITH sessions AS
(
    SELECT
        ticker,
        date,
        toFloat64(open)  AS open_px,
        toFloat64(high)  AS high_px,
        toFloat64(close) AS close_px,
        row_number() OVER (PARTITION BY ticker ORDER BY date)     AS session_n,
        row_number() OVER (PARTITION BY ticker ORDER BY date) + 1 AS next_session_n
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO')
      AND date >= '2021-08-01'
      AND date <  '2026-08-01'
)
SELECT
    cur.ticker                                                                AS symbol,
    count()                                                                   AS gapped_through_sessions,
    round(100 * countIf(cur.high_px >= prior.close_px * 0.9702) / count(), 1) AS reached_limit_intraday_pct
FROM sessions AS cur
INNER JOIN sessions AS prior
    ON cur.ticker = prior.ticker
   AND cur.session_n = prior.next_session_n
WHERE cur.open_px < prior.close_px * 0.9702
  AND (cur.ticker, cur.date) NOT IN
  (
      SELECT ticker, execution_date
      FROM global_markets.stocks_splits
  )
GROUP BY symbol
ORDER BY reached_limit_intraday_pct DESC