AAPL contracts trading on their own expiration day: share finishing out of the money, six monthly cycles

WITH traded AS (
    SELECT toDate(sip_timestamp) AS session_date,
           ticker
    FROM global_markets.options_trades
    WHERE ticker LIKE 'O:AAPL2%'
      AND toDate(sip_timestamp) IN ('2025-12-19', '2026-01-16', '2026-02-20', '2026-03-20', '2026-04-17', '2026-05-15')
    GROUP BY session_date, ticker
),
expiring AS (
    SELECT session_date AS expiration_date,
           substring(ticker, 13, 1) AS right_code,
           toFloat64OrZero(substring(ticker, 14, 8)) / 1000 AS strike_usd
    FROM traded
    WHERE toDate(concat('20', substring(ticker, 7, 2), '-', substring(ticker, 9, 2), '-', substring(ticker, 11, 2))) = session_date
),
spot AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS expiration_date,
           argMax(toFloat64(close), window_start) AS closing_price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'AAPL'
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN ('2025-12-19', '2026-01-16', '2026-02-20', '2026-03-20', '2026-04-17', '2026-05-15')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY expiration_date
)
SELECT formatDateTimeInJodaSyntax(e.expiration_date, 'MMM yyyy') AS cycle,
       count() AS contract_count,
       round(100 * countIf(e.right_code = 'C' AND e.strike_usd > s.closing_price) / countIf(e.right_code = 'C'), 1) AS calls_otm_pct,
       round(100 * countIf(e.right_code = 'P' AND e.strike_usd < s.closing_price) / countIf(e.right_code = 'P'), 1) AS puts_otm_pct
FROM expiring AS e
INNER JOIN spot AS s ON e.expiration_date = s.expiration_date
GROUP BY e.expiration_date
HAVING countIf(e.right_code = 'C') > 0 AND countIf(e.right_code = 'P') > 0
ORDER BY e.expiration_date