3,256 answered market questions

WITH
    (
        SELECT max(date)
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'NKE'
          AND iv_converged = 1
          AND volume > 0
    ) AS asof_date,
    (
        SELECT expiration_date
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'NKE'
          AND lower(toString(option_type)) IN ('call', 'c')
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 60 AND 90
          AND date = (
              SELECT max(date)
              FROM global_markets.options_greeks
              WHERE underlying_symbol = 'NKE'
                AND iv_converged = 1
                AND volume > 0
          )
        GROUP BY expiration_date
        ORDER BY sum(volume) DESC, expiration_date
        LIMIT 1
    ) AS expiry,
    (
        SELECT toFloat64(max(close))
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'NKE'
          AND date >= today() - 371
          AND date <  today()
    ) AS purchase_price
SELECT
    label,
    round(raw_value, 2)    AS per_share,
    round(raw_pct, 1)      AS vs_spot_pct
FROM
(
    SELECT
        any(spot)          AS spot_px,
        any(atm_k)         AS k1,
        any(atm_px)        AS k1_px,
        max(k)             AS k2,
        argMax(px, k)      AS k2_px
    FROM
    (
        SELECT
            k,
            px,
            spot,
            first_value(k)  OVER (ORDER BY abs(k - spot), k ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS atm_k,
            first_value(px) OVER (ORDER BY abs(k - spot), k ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS atm_px
        FROM
        (
            SELECT
                toFloat64(strike_price)              AS k,
                toFloat64(any(option_close))         AS px,
                toFloat64(any(underlying_close))     AS spot
            FROM global_markets.options_greeks
            WHERE underlying_symbol = 'NKE'
              AND lower(toString(option_type)) IN ('call', 'c')
              AND date = asof_date
              AND expiration_date = expiry
              AND iv_converged = 1
              AND volume > 0
            GROUP BY strike_price
        )
    )
    WHERE k > atm_k
      AND k <= atm_k * 1.30
      AND atm_px - 2 * px <= 0
) AS pick
ARRAY JOIN
    [0, 1, 2, 3, 4, 5, 6, 7, 8] AS ord,
    ['Current share price',
     'Long call strike (at the money)',
     'Long call price',
     'Short call strike (the recovery cap)',
     'Short call price (each of the two)',
     'Net cost of the 1x2 per share (negative = credit)',
     'Repair breakeven',
     'Original purchase price (hold-and-hope breakeven)',
     'Crossover price (plain shares pull ahead above this)'] AS label,
    [spot_px,
     k1,
     k1_px,
     k2,
     k2_px,
     k1_px - 2 * k2_px,
     (purchase_price + k1 + (k1_px - 2 * k2_px)) / 2,
     purchase_price,
     2 * k2 - k1 - (k1_px - 2 * k2_px)] AS raw_value,
    [0,
     (k1 / spot_px - 1) * 100,
     k1_px / spot_px * 100,
     (k2 / spot_px - 1) * 100,
     k2_px / spot_px * 100,
     (k1_px - 2 * k2_px) / spot_px * 100,
     ((purchase_price + k1 + (k1_px - 2 * k2_px)) / 2 / spot_px - 1) * 100,
     (purchase_price / spot_px - 1) * 100,
     ((2 * k2 - k1 - (k1_px - 2 * k2_px)) / spot_px - 1) * 100] AS raw_pct
ORDER BY ord