Sessions with a 10%+ drop from the prior close, September 2024 through August 2026

WITH daily AS
(
    SELECT
        ticker,
        date,
        toFloat64(min(low))   AS session_low,
        toFloat64(any(close)) AS session_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'TSLA', 'AMD', 'PLTR')
      AND date >= '2024-09-01'
      AND date <  '2026-09-01'
      AND low > 0
    GROUP BY ticker, date
),
with_prior AS
(
    SELECT
        ticker,
        session_low,
        lagInFrame(session_close, 1) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prior_close
    FROM daily
)
SELECT
    ticker,
    countIf(session_low <= prior_close * 0.9)                           AS rule_201_trigger_days,
    count()                                                             AS session_count,
    round(100 * countIf(session_low <= prior_close * 0.9) / count(), 2) AS trigger_rate_pct
FROM with_prior
WHERE prior_close > 0
GROUP BY ticker
ORDER BY rule_201_trigger_days DESC, ticker