2,401 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
    toString(k)                                                     AS strike,
    round((k / spot - 1) * 100, 1)                                  AS pct_above_spot,
    round(px, 2)                                                    AS short_call_price,
    round(atm_px - 2 * px, 2)                                       AS net_cost_1x2,
    round(2 * k - atm_k - (atm_px - 2 * px) - purchase_price, 2)    AS max_pnl_per_share,
    concat(formatDateTime(asof_date, '%b'), ' ', toString(toDayOfMonth(asof_date)), ', ', toString(toYear(asof_date))) AS as_of,
    concat(formatDateTime(expiry, '%b'), ' ', toString(toDayOfMonth(expiry)), ', ', toString(toYear(expiry)),
           ' (', toString(dateDiff('day', asof_date, expiry)), ' days out)')                                          AS expiry_label
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
ORDER BY k