spy_put_daily_moves

WITH daily AS
(
    SELECT
        ticker,
        date,
        max(toFloat64(option_close))     AS put_close,
        max(toFloat64(underlying_close)) AS spy_close,
        max(toFloat64(strike_price))     AS strike,
        max(days_to_expiry)              AS dte
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(toString(option_type)) IN ('put', 'p')
      AND date >= toDate('2026-06-29')
      AND date <  toDate('2026-08-01')
      AND days_to_expiry BETWEEN 15 AND 50
      AND volume > 0
    GROUP BY ticker, date
),
chained AS
(
    SELECT
        date,
        put_close,
        spy_close,
        strike,
        dte,
        lagInFrame(put_close, 1) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_put,
        lagInFrame(date, 1)      OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_date
    FROM daily
),
moves AS
(
    SELECT abs(100 * (put_close / prev_put - 1)) AS move_pct
    FROM chained
    WHERE prev_put > 0
      AND dateDiff('day', prev_date, date) <= 4
      AND date >= toDate('2026-07-01')
      AND dte BETWEEN 20 AND 45
      AND abs(strike / spy_close - 1) < 0.02
)
SELECT
    multiIf(move_pct < 5,  'under 5%',
            move_pct < 10, '5% to 10%',
            move_pct < 20, '10% to 20%',
                           '20% or more') AS move_bucket,
    count()                                               AS contract_days,
    round(100 * count() / (SELECT count() FROM moves), 1) AS share_pct
FROM moves
GROUP BY move_bucket
ORDER BY min(move_pct)