3,094 answered market questions

WITH
    (
        SELECT max(date)
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
    ) AS snap_date,
    (
        SELECT argMin(expiration_date, abs(toInt32(days_to_expiry) - 35))
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date = snap_date
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 20 AND 60
    ) AS expiry
SELECT
    concat('+', toString(shock_pts), ' vol pts')  AS vol_rise,
    round(0.5 * wing_growth * shock_pts, 1)       AS wing_uplift_pct,
    round(0.5 * atm_growth * shock_pts, 1)        AS atm_uplift_pct
FROM
(
    SELECT
        avgIf(d1 * d2 / sigma, abs(k) > 0.05 AND abs(k) <= 0.12) AS wing_growth,
        avgIf(d1 * d2 / sigma, abs(k) <= 0.02)                   AS atm_growth
    FROM
    (
        SELECT
            k,
            sigma,
            d1,
            d1 - sigma * sqrt(t_years) AS d2
        FROM
        (
            SELECT
                k,
                sigma,
                t_years,
                (log(spot / strike) + (rate + sigma * sigma / 2) * t_years)
                    / (sigma * sqrt(t_years)) AS d1
            FROM
            (
                SELECT
                    toFloat64(underlying_close)                               AS spot,
                    toFloat64(strike_price)                                   AS strike,
                    toFloat64(strike_price) / toFloat64(underlying_close) - 1 AS k,
                    toFloat64(implied_volatility)                             AS sigma,
                    days_to_expiry / 365.0                                    AS t_years,
                    if(toFloat64(risk_free_rate) > 1,
                       toFloat64(risk_free_rate) / 100,
                       toFloat64(risk_free_rate))                             AS rate
                FROM global_markets.options_greeks
                WHERE underlying_symbol = 'SPY'
                  AND date = snap_date
                  AND expiration_date = expiry
                  AND iv_converged = 1
                  AND volume > 0
                  AND vega > 0
                  AND days_to_expiry >= 7
                  AND implied_volatility BETWEEN 0.02 AND 3.0
            )
        )
    )
    HAVING countIf(abs(k) > 0.05 AND abs(k) <= 0.12) > 0
       AND countIf(abs(k) <= 0.02) > 0
) AS chain
CROSS JOIN
(
    SELECT arrayJoin([1, 2, 3, 5, 8, 10, 15]) AS shock_pts
) AS shocks
ORDER BY shock_pts