옵션 변동성 호가 방식과 가격 결정 원리

SELECT
    et_hour,
    round(quantileDeterministic(0.5)(move_bps, obs_id), 1) AS median_move_bps,
    round(quantileDeterministic(0.9)(move_bps, obs_id), 1) AS p90_move_bps,
    count()                                                AS minute_bars
FROM
(
    SELECT
        formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:00') AS et_hour,
        cityHash64(ticker, window_start)                                      AS obs_id,
        10000 * abs(toFloat64(close) / toFloat64(open) - 1)                   AS move_bps
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'AAPL'
      AND window_start >= '2026-04-06 04:00:00'
      AND window_start <  '2026-05-16 04:00:00'
      AND open > 0
      AND volume > 0
)
GROUP BY et_hour
HAVING count() >= 1200
ORDER BY et_hour ASC

WITH
(
    SELECT ticker
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND toFloat64(delta) > 0
      AND date BETWEEN '2026-04-06' AND '2026-05-15'
      AND expiration_date BETWEEN '2026-06-15' AND '2026-09-30'
      AND iv_converged = 1
      AND volume > 0
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.03
    GROUP BY ticker
    ORDER BY sum(volume) DESC
    LIMIT 1
) AS traced_call
SELECT
    toString(date)                     AS session_date,
    formatDateTime(date, '%b %e')      AS session_label,
    round(100 * (toFloat64(underlying_close)
        / first_value(toFloat64(underlying_close)) OVER (ORDER BY date ASC) - 1), 2)   AS stock_change_pct,
    round(100 * (toFloat64(option_close)
        / first_value(toFloat64(option_close)) OVER (ORDER BY date ASC) - 1), 2)       AS call_premium_change_pct,
    round(100 * (toFloat64(implied_volatility)
        / first_value(toFloat64(implied_volatility)) OVER (ORDER BY date ASC) - 1), 2) AS quoted_vol_change_pct
FROM global_markets.options_greeks
WHERE ticker = traced_call
  AND date BETWEEN '2026-04-06' AND '2026-05-15'
  AND iv_converged = 1
  AND option_close > 0
  AND implied_volatility > 0
ORDER BY session_date ASC

SELECT
    strike_vs_spot,
    round(100 * quantileDeterministic(0.5)(iv, obs_id), 2)       AS quoted_vol_pct,
    round(quantileDeterministic(0.5)(contract_delta, obs_id), 2) AS typical_delta
FROM
(
    SELECT
        cityHash64(ticker, date)      AS obs_id,
        toFloat64(implied_volatility) AS iv,
        toFloat64(delta)              AS contract_delta,
        toInt32(round(100 * (toFloat64(strike_price) / toFloat64(underlying_close) - 1) / 2) * 2) AS moneyness_step,
        concat(if(moneyness_step > 0, '+', ''), toString(moneyness_step), '%')                    AS strike_vs_spot
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND toFloat64(delta) > 0
      AND date BETWEEN '2026-04-06' AND '2026-05-15'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 20 AND 45
      AND implied_volatility > 0
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) <= 0.10
)
GROUP BY strike_vs_spot, moneyness_step
HAVING count() >= 20
ORDER BY moneyness_step ASC

SELECT
    underlying_symbol                                              AS symbol,
    round(quantileDeterministic(0.5)(premium_move_pct, obs_id), 2) AS median_premium_move_pct,
    round(quantileDeterministic(0.5)(vol_move_pct, obs_id), 2)     AS median_vol_move_pct,
    round(quantileDeterministic(0.5)(premium_move_pct, obs_id)
        / quantileDeterministic(0.5)(vol_move_pct, obs_id), 1)     AS premium_move_ratio
FROM
(
    SELECT
        underlying_symbol,
        cityHash64(ticker, date)                                AS obs_id,
        100 * abs(toFloat64(option_close) / prev_premium - 1)   AS premium_move_pct,
        100 * abs(toFloat64(implied_volatility) / prev_vol - 1) AS vol_move_pct
    FROM
    (
        SELECT
            underlying_symbol,
            ticker,
            date,
            option_close,
            implied_volatility,
            lagInFrame(toFloat64(option_close))       OVER (PARTITION BY ticker ORDER BY date ASC) AS prev_premium,
            lagInFrame(toFloat64(implied_volatility)) OVER (PARTITION BY ticker ORDER BY date ASC) AS prev_vol,
            lagInFrame(date)                          OVER (PARTITION BY ticker ORDER BY date ASC) AS prev_date
        FROM global_markets.options_greeks
        WHERE underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'JNJ')
          AND date BETWEEN '2026-04-06' AND '2026-05-15'
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 20 AND 45
          AND option_close > 0
          AND implied_volatility > 0
          AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
    )
    WHERE prev_premium > 0
      AND prev_vol > 0
      AND dateDiff('day', prev_date, date) <= 4
)
GROUP BY underlying_symbol
HAVING count() >= 200
ORDER BY premium_move_ratio ASC