spy_put_trace

WITH pick AS
(
    SELECT ticker
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(toString(option_type)) IN ('put', 'p')
      AND date = toDate('2026-07-06')
      AND days_to_expiry BETWEEN 25 AND 35
      AND volume > 0
    ORDER BY abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) ASC,
             expiration_date ASC,
             ticker ASC
    LIMIT 1
),
daily AS
(
    SELECT
        date,
        max(toFloat64(option_close))     AS put_close_raw,
        max(toFloat64(underlying_close)) AS spy_close_raw
    FROM global_markets.options_greeks
    WHERE ticker IN (SELECT ticker FROM pick)
      AND date >= toDate('2026-07-02')
      AND date <  toDate('2026-07-18')
      AND volume > 0
    GROUP BY date
),
chained AS
(
    SELECT
        date,
        put_close_raw,
        spy_close_raw,
        lagInFrame(put_close_raw, 1) OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_put,
        lagInFrame(spy_close_raw, 1) OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_spy
    FROM daily
)
SELECT
    toString(date)                                                        AS session_date,
    concat(formatDateTime(date, '%b'), ' ', toString(toDayOfMonth(date))) AS calendar_label,
    round(put_close_raw, 2)                                               AS put_close,
    round(100 * (put_close_raw / prev_put - 1), 1)                        AS put_move_pct,
    round(100 * (spy_close_raw / prev_spy - 1), 2)                        AS spy_move_pct
FROM chained
WHERE prev_put > 0
  AND prev_spy > 0
  AND date >= toDate('2026-07-06')
ORDER BY date