2,170 answered market questions

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
)