S&P 500 tracker moves after each fifth of the equity put/call ratio, 2022 to July 2026

WITH raw AS (
    SELECT date AS day,
           sumIf(volume, upper(substring(ticker, length(ticker) - 8, 1)) = 'P') AS put_volume,
           sumIf(volume, upper(substring(ticker, length(ticker) - 8, 1)) = 'C') AS call_volume
    FROM global_markets.options_greeks
    WHERE date >= toDate('2022-01-01')
      AND date <= toDate('2026-07-31')
      AND volume > 0
      AND underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'META',
                                'TSLA', 'GOOGL', 'JPM', 'KO', 'XOM')
    GROUP BY day
    HAVING call_volume > 0
),
equity_pcr AS (
    SELECT day, toFloat64(put_volume) / toFloat64(call_volume) AS pcr
    FROM raw
),
spy_daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           toFloat64(argMax(close, window_start)) AS close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2022-01-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-08-01')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY day
),
forward AS (
    SELECT day,
           close,
           any(close) OVER (ORDER BY day ASC ROWS BETWEEN 5 FOLLOWING AND 5 FOLLOWING) AS close_5,
           any(close) OVER (ORDER BY day ASC ROWS BETWEEN 20 FOLLOWING AND 20 FOLLOWING) AS close_20
    FROM spy_daily
),
joined AS (
    SELECT p.day AS day,
           p.pcr AS pcr,
           100 * (f.close_5 / f.close - 1) AS fwd_5d_pct,
           100 * (f.close_20 / f.close - 1) AS fwd_20d_pct
    FROM equity_pcr AS p
    INNER JOIN forward AS f ON p.day = f.day
    WHERE f.close_5 > 0 AND f.close_20 > 0
),
cuts AS (
    SELECT quantilesDeterministic(0.2, 0.4, 0.6, 0.8)(pcr, cityHash64(day)) AS q
    FROM joined
)
SELECT multiIf(pcr < q[1], 'lowest fifth',
               pcr < q[2], 'second fifth',
               pcr < q[3], 'middle fifth',
               pcr < q[4], 'fourth fifth',
               'highest fifth') AS pcr_band,
       count() AS observations,
       round(min(pcr), 2) AS band_floor_ratio,
       round(quantileDeterministic(0.5)(fwd_5d_pct, cityHash64(day)), 2) AS fwd_5d_median_pct,
       round(quantileDeterministic(0.5)(fwd_20d_pct, cityHash64(day)), 2) AS fwd_20d_median_pct,
       round(quantileDeterministic(0.25)(fwd_20d_pct, cityHash64(day)), 2) AS fwd_20d_p25_pct,
       round(quantileDeterministic(0.75)(fwd_20d_pct, cityHash64(day)), 2) AS fwd_20d_p75_pct
FROM joined CROSS JOIN cuts
GROUP BY pcr_band
ORDER BY band_floor_ratio ASC