3,256 answered market questions

SELECT
    formatDateTime(expiration_date, '%b %Y')                        AS expiry,
    round(avg(rate_pct), 2)                                         AS parity_rate_pct,
    concat(toString(round(avg(term_days) / 30.44, 1)), ' months')   AS tenor_label,
    toUInt32(count())                                               AS strike_count
FROM
(
    SELECT
        expiration_date,
        strike_price,
        max(days_to_expiry) AS term_days,
        100 * log(toFloat64(strike_price) / (avg(toFloat64(underlying_close))
            - avgIf(toFloat64(option_close), leg = 'call')
            + avgIf(toFloat64(option_close), leg = 'put')))
            / (max(days_to_expiry) / 365.0) AS rate_pct
    FROM
    (
        SELECT
            expiration_date,
            strike_price,
            underlying_close,
            option_close,
            days_to_expiry,
            if(lower(toString(option_type)) LIKE 'c%', 'call', 'put') AS leg
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'AAPL'
          AND date = (
                SELECT max(date)
                FROM global_markets.options_greeks
                WHERE underlying_symbol = 'AAPL'
          )
          AND toFloat64(option_close) > 0
          AND days_to_expiry BETWEEN 45 AND 600
          AND toDayOfWeek(expiration_date) = 5
          AND toDayOfMonth(expiration_date) BETWEEN 15 AND 21
          AND toFloat64(strike_price) / toFloat64(underlying_close) BETWEEN 0.60 AND 0.90
    )
    GROUP BY expiration_date, strike_price
    HAVING countIf(leg = 'call') > 0
       AND countIf(leg = 'put') > 0
)
GROUP BY expiration_date
HAVING count() >= 2
ORDER BY expiration_date
LIMIT 12