האם יחס put/call גבוה הוא שורי?

WITH book_days AS (
    SELECT date AS day,
           if(underlying_symbol IN ('SPY', 'QQQ', 'IWM'), 'index_etf', 'equity') AS book,
           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 ('SPY', 'QQQ', 'IWM', 'AAPL', 'MSFT', 'NVDA', 'AMZN',
                                'META', 'TSLA', 'GOOGL', 'JPM', 'KO', 'XOM')
    GROUP BY day, book
    HAVING call_volume > 0
),
paired AS (
    SELECT day,
           toFloat64(sumIf(put_volume, book = 'equity'))
             / toFloat64(sumIf(call_volume, book = 'equity')) AS equity_pcr,
           toFloat64(sumIf(put_volume, book = 'index_etf'))
             / toFloat64(sumIf(call_volume, book = 'index_etf')) AS index_pcr
    FROM book_days
    GROUP BY day
    HAVING sumIf(call_volume, book = 'equity') > 0
       AND sumIf(call_volume, book = 'index_etf') > 0
),
curves AS (
    SELECT arrayMap(v -> round(v, 2),
                    quantilesDeterministic(0.05, 0.10, 0.25, 0.50, 0.75, 0.90, 0.95, 0.99)
                                          (equity_pcr, cityHash64(day))) AS eq,
           arrayMap(v -> round(v, 2),
                    quantilesDeterministic(0.05, 0.10, 0.25, 0.50, 0.75, 0.90, 0.95, 0.99)
                                          (index_pcr, cityHash64(day))) AS idx
    FROM paired
)
SELECT z.1 AS percentile,
       z.2 AS equity_pcr,
       z.3 AS index_etf_pcr
FROM (
    SELECT arrayJoin(arrayZip(['p05', 'p10', 'p25', 'p50', 'p75', 'p90', 'p95', 'p99'], eq, idx)) AS z
    FROM curves
)

WITH symbol_days AS (
    SELECT underlying_symbol AS symbol,
           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('2026-01-01')
      AND date <= toDate('2026-07-31')
      AND volume > 0
      AND underlying_symbol IN ('SPY', 'QQQ', 'IWM', 'AAPL', 'MSFT', 'NVDA', 'AMZN',
                                'META', 'TSLA', 'GOOGL', 'JPM', 'KO', 'XOM')
    GROUP BY symbol, day
    HAVING call_volume > 0
)
SELECT symbol,
       round(toFloat64(sum(put_volume)) / toFloat64(sum(call_volume)), 2) AS put_call_ratio,
       round(100 * countIf(put_volume > call_volume) / count(), 1) AS above_one_pct,
       count() AS observations
FROM symbol_days
GROUP BY symbol
ORDER BY put_call_ratio DESC

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

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
),
cut AS (
    SELECT quantileDeterministic(0.90)(pcr, cityHash64(day)) AS p90
    FROM equity_pcr
),
by_month AS (
    SELECT toStartOfMonth(day) AS month_start,
           countIf(pcr >= cut.p90) AS extreme_reads,
           quantileDeterministic(0.5)(pcr, cityHash64(day)) AS median_pcr
    FROM equity_pcr CROSS JOIN cut
    GROUP BY month_start
),
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-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY day
),
spy_month AS (
    SELECT toStartOfMonth(day) AS month_start,
           100 * (argMax(close, day) / argMin(close, day) - 1) AS move_pct
    FROM spy_daily
    GROUP BY month_start
)
SELECT multiIf(s.move_pct <= -3, 'index down 3% or more',
               s.move_pct < 0, 'index down under 3%',
               s.move_pct < 3, 'index up under 3%',
               'index up 3% or more') AS index_move_bucket,
       count() AS observations,
       round(avg(m.extreme_reads), 1) AS top_decile_reads_avg,
       round(quantileDeterministic(0.5)(m.median_pcr, cityHash64(m.month_start)), 2) AS typical_pcr_ratio
FROM by_month AS m
INNER JOIN spy_month AS s ON m.month_start = s.month_start
GROUP BY index_move_bucket
ORDER BY min(s.move_pct) ASC