chain_ladder

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH snapshot AS
(
    SELECT max(date) AS as_of
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'JNJ'
),
expiry_pick AS
(
    SELECT min(expiration_date) AS expiry
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'JNJ'
      AND date = (SELECT as_of FROM snapshot)
      AND days_to_expiry BETWEEN 20 AND 45
      AND volume > 0
),
rungs AS
(
    SELECT
        toFloat64(strike_price)                                                       AS strike,
        round(avgIf(toFloat64(option_close), lower(option_type) IN ('call', 'c')), 2) AS call_premium_usd,
        round(avgIf(toFloat64(option_close), lower(option_type) IN ('put', 'p')), 2)  AS put_premium_usd
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'JNJ'
      AND date = (SELECT as_of FROM snapshot)
      AND expiration_date = (SELECT expiry FROM expiry_pick)
      AND iv_converged = 1
      AND volume > 0
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.08
    GROUP BY strike
    HAVING countIf(lower(option_type) IN ('call', 'c')) > 0
       AND countIf(lower(option_type) IN ('put', 'p')) > 0
)
SELECT
    toString(strike)                                            AS strike_usd,
    call_premium_usd,
    put_premium_usd,
    round(strike - lagInFrame(strike) OVER (ORDER BY strike), 2) AS gap_usd
FROM rungs
ORDER BY strike