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
    concat(if(m > 1, '+', ''), toString(toInt32(round((m - 1) * 100))), '%')   AS move,
    toString(round(spot_px * m, 2))                                           AS price_at_expiry,
    round(spot_px * m - purchase_price, 2)                                    AS hold_pnl_per_share,
    round(spot_px * m - purchase_price
          + greatest(spot_px * m - k1, 0.0)
          - 2 * greatest(spot_px * m - k2, 0.0)
          - (k1_px - 2 * k2_px), 2)                                           AS repair_pnl_per_share
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.80, 0.85, 0.90, 0.95, 1.00, 1.05, 1.10, 1.15, 1.20, 1.25, 1.30] AS m
ORDER BY m