Covered Calls on AAPL: A Worked Example

WITH
    (
        SELECT max(date)
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'AAPL'
          AND lower(toString(option_type)) IN ('call', 'c')
          AND toFloat64(option_close) > 0
    ) AS snap_date,
    (
        SELECT argMin(expiration_date, abs(toInt32(days_to_expiry) - 30))
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'AAPL'
          AND lower(toString(option_type)) IN ('call', 'c')
          AND toFloat64(option_close) > 0
          AND date = snap_date
          AND toInt32(days_to_expiry) BETWEEN 21 AND 45
    ) AS write_expiry
SELECT
    delta_target,
    toString(round(argMin(strike_usd, gap), 1)) AS strike,
    round(argMin(delta_measured, gap), 3) AS call_delta,
    round(argMin(premium_usd, gap), 2) AS premium,
    toString(round(argMin(premium_usd, gap) * 100, 0)) AS premium_per_contract,
    round(argMin(static_pct, gap), 2) AS static_return_pct,
    round(argMin(called_pct, gap), 2) AS if_called_pct,
    toString(round(argMin(breakeven_usd, gap), 2)) AS breakeven,
    toString(argMin(dte, gap)) AS days_to_expiry,
    argMin(as_of, gap) AS as_of
FROM
(
    SELECT
        tg.delta_target AS delta_target,
        abs(toFloat64(g.delta) - toFloat64(tg.delta_target)) AS gap,
        toFloat64(g.strike_price) AS strike_usd,
        toFloat64(g.delta) AS delta_measured,
        toFloat64(g.option_close) AS premium_usd,
        100 * toFloat64(g.option_close) / toFloat64(g.underlying_close) AS static_pct,
        100 * (toFloat64(g.option_close) + toFloat64(g.strike_price) - toFloat64(g.underlying_close))
            / toFloat64(g.underlying_close) AS called_pct,
        toFloat64(g.underlying_close) - toFloat64(g.option_close) AS breakeven_usd,
        toInt32(g.days_to_expiry) AS dte,
        concat(monthName(toDate(g.date)), ' ', toString(toDayOfMonth(toDate(g.date))), ', ', toString(toYear(toDate(g.date)))) AS as_of
    FROM global_markets.options_greeks AS g
    CROSS JOIN
    (
        SELECT arrayJoin(['0.15', '0.20', '0.25', '0.30', '0.35', '0.40']) AS delta_target
    ) AS tg
    WHERE g.underlying_symbol = 'AAPL'
      AND lower(toString(g.option_type)) IN ('call', 'c')
      AND g.date = snap_date
      AND g.expiration_date = write_expiry
      AND toFloat64(g.option_close) > 0
      AND toFloat64(g.delta) BETWEEN 0.05 AND 0.60
)
GROUP BY delta_target
ORDER BY delta_target

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

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)
),
picks AS
(
    SELECT
        delta_target,
        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
            tg.delta_target AS delta_target,
            toStartOfMonth(g.date) AS write_month,
            (abs(toInt32(g.days_to_expiry) - 30),
             abs(toFloat64(g.delta) - toFloat64(tg.delta_target))) AS gap,
            toFloat64(g.strike_price) AS strike_usd,
            g.expiration_date AS expiry,
            toFloat64(g.option_close) AS premium_usd,
            toFloat64(g.underlying_close) AS spot_usd
        FROM global_markets.options_greeks AS g
        CROSS JOIN
        (
            SELECT arrayJoin(['0.15', '0.20', '0.25', '0.30', '0.35', '0.40']) AS delta_target
        ) AS tg
        WHERE g.underlying_symbol = 'AAPL'
          AND lower(toString(g.option_type)) IN ('call', 'c')
          AND toFloat64(g.option_close) > 0
          AND toInt32(g.days_to_expiry) BETWEEN 21 AND 45
          AND toFloat64(g.delta) BETWEEN 0.05 AND 0.55
          AND g.expiration_date <= today() - 3
          AND g.date IN (SELECT write_date FROM month_open)
    )
    GROUP BY delta_target, write_month
)
SELECT
    p.delta_target AS delta_target,
    count() AS write_count,
    round(avg(100 * p.premium_usd / p.spot_usd), 2) AS avg_premium_pct,
    round(100 * countIf(toFloat64(s.close) > p.strike_usd) / count(), 1) AS finished_itm_pct,
    round(avg(100 * (least(toFloat64(s.close), p.strike_usd) + p.premium_usd - p.spot_usd) / p.spot_usd), 2) AS avg_write_return_pct,
    round(avg(100 * (toFloat64(s.close) - p.spot_usd) / p.spot_usd), 2) AS avg_hold_return_pct
FROM picks 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
GROUP BY p.delta_target
ORDER BY p.delta_target

WITH ex_dates AS
(
    SELECT
        toDate(ex_dividend_date) AS ex_date,
        max(toFloat64(cash_amount)) AS dividend
    FROM global_markets.stocks_dividends
    WHERE ticker = 'AAPL'
      AND toDate(ex_dividend_date) >= toDate('2021-09-01')
      AND toDate(ex_dividend_date) < today()
    GROUP BY ex_date
),
option_sessions AS
(
    SELECT DISTINCT toDate(date) AS session_date
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= '2021-08-01'
),
cutoffs AS
(
    SELECT
        e.ex_date AS ex_date,
        e.dividend AS dividend,
        max(o.session_date) AS last_session_before
    FROM ex_dates AS e
    CROSS JOIN option_sessions AS o
    WHERE o.session_date < e.ex_date
    GROUP BY e.ex_date, e.dividend
),
picked AS
(
    SELECT
        ex_date,
        dividend,
        argMin(time_value, gap) AS time_value,
        argMin(strike_usd, gap) AS strike_usd,
        argMin(dte, gap) AS dte
    FROM
    (
        SELECT
            c.ex_date AS ex_date,
            c.dividend AS dividend,
            (toInt32(g.days_to_expiry),
             toFloat64(g.underlying_close) - toFloat64(g.strike_price)) AS gap,
            toFloat64(g.option_close)
              - (toFloat64(g.underlying_close) - toFloat64(g.strike_price)) AS time_value,
            toFloat64(g.strike_price) AS strike_usd,
            toInt32(g.days_to_expiry) AS dte
        FROM global_markets.options_greeks AS g
        INNER JOIN cutoffs AS c ON toDate(g.date) = c.last_session_before
        WHERE g.underlying_symbol = 'AAPL'
          AND lower(toString(g.option_type)) IN ('call', 'c')
          AND toFloat64(g.option_close) > 0
          AND toDate(g.expiration_date) > c.ex_date
          AND toInt32(g.days_to_expiry) <= 45
          AND toFloat64(g.strike_price) < toFloat64(g.underlying_close)
          AND toFloat64(g.strike_price) > toFloat64(g.underlying_close) * 0.97
    )
    GROUP BY ex_date, dividend
)
SELECT
    toString(ex_date) AS ex_date_iso,
    concat(monthName(ex_date), ' ', toString(toDayOfMonth(ex_date)), ', ', toString(toYear(ex_date))) AS ex_pretty,
    round(dividend, 2) AS dividend_usd,
    round(time_value, 2) AS time_value_usd,
    toString(round(strike_usd, 1)) AS strike,
    toString(dte) AS days_to_expiry,
    if(time_value < dividend, 'the dividend was larger', 'the time value was larger') AS exercise_edge
FROM picked
ORDER BY ex_date

WITH
    (
        SELECT max(date)
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'AAPL'
          AND lower(toString(option_type)) IN ('call', 'c')
          AND toFloat64(option_close) > 0
    ) AS snap_date
SELECT
    underlying_symbol AS symbol,
    round(100 * avg(toFloat64(implied_volatility)), 1) AS iv_pct,
    round(100 * avg(toFloat64(option_close) / toFloat64(underlying_close)), 2) AS premium_pct
FROM global_markets.options_greeks
WHERE date = snap_date
  AND lower(toString(option_type)) IN ('call', 'c')
  AND toFloat64(option_close) > 0
  AND toFloat64(implied_volatility) > 0
  AND underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'TSLA', 'SPY', 'KO')
  AND underlying_symbol NOT IN ('SPCX')
  AND toInt32(days_to_expiry) BETWEEN 21 AND 45
  AND toFloat64(delta) BETWEEN 0.25 AND 0.35
GROUP BY symbol
ORDER BY symbol = 'AAPL' DESC, iv_pct DESC