3,256 answered market questions

WITH month_open AS
(
    SELECT min(date) AS write_date
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND lower(toString(option_type)) IN ('call', 'c')
      AND toFloat64(option_close) > 0
      AND date >= '2021-09-01'
    GROUP BY toStartOfMonth(date)
),
picked AS
(
    SELECT
        write_month,
        argMin(strike_usd, gap) AS strike_usd,
        argMin(expiry, gap) AS expiry,
        argMin(premium_usd, gap) AS premium_usd,
        argMin(spot_usd, gap) AS spot_usd
    FROM
    (
        SELECT
            toStartOfMonth(date) AS write_month,
            (abs(toInt32(days_to_expiry) - 30), abs(toFloat64(delta) - 0.30)) AS gap,
            toFloat64(strike_price) AS strike_usd,
            expiration_date AS expiry,
            toFloat64(option_close) AS premium_usd,
            toFloat64(underlying_close) AS spot_usd
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'AAPL'
          AND lower(toString(option_type)) IN ('call', 'c')
          AND toFloat64(option_close) > 0
          AND toInt32(days_to_expiry) BETWEEN 21 AND 45
          AND toFloat64(delta) BETWEEN 0.10 AND 0.50
          AND expiration_date <= today() - 3
          AND date IN (SELECT write_date FROM month_open)
    )
    GROUP BY write_month
)
SELECT
    formatDateTime(p.write_month, '%Y-%m') AS month,
    concat(monthName(p.write_month), ' ', toString(toYear(p.write_month))) AS month_label,
    toString(round(p.strike_usd, 1)) AS strike,
    round(p.premium_usd, 2) AS premium,
    round(100 * p.premium_usd / p.spot_usd, 2) AS premium_pct,
    round(100 * (toFloat64(s.close) / p.spot_usd - 1), 2) AS move_to_expiry_pct,
    if(toFloat64(s.close) > p.strike_usd, 'called away', 'kept') AS outcome
FROM picked AS p
INNER JOIN
(
    SELECT date, close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2021-09-01'
) AS s ON s.date = p.expiry
ORDER BY p.write_month