2,309 answered market questions

WITH busiest AS (
    SELECT ticker
    FROM global_markets.options_trades
    WHERE ticker LIKE 'O:AAPL260515C%'
      AND toDate(sip_timestamp) BETWEEN toDate('2026-04-15') AND toDate('2026-05-15')
    GROUP BY ticker
    ORDER BY count() DESC
    LIMIT 1
),
contract AS (
    SELECT toFloat64OrZero(substring(ticker, 14, 8)) / 1000 AS strike_usd,
           concat('$', toString(round(toFloat64OrZero(substring(ticker, 14, 8)) / 1000, 2)), ' strike') AS contract_label
    FROM busiest
),
premium AS (
    SELECT toDate(sip_timestamp) AS date,
           round(argMax(toFloat64(price), sip_timestamp), 2) AS premium_usd
    FROM global_markets.options_trades
    WHERE ticker IN (SELECT ticker FROM busiest)
      AND toDate(sip_timestamp) BETWEEN toDate('2026-04-15') AND toDate('2026-05-15')
    GROUP BY date
),
spot AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS date,
           argMax(toFloat64(close), window_start) AS stock_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'AAPL'
      AND toDate(toTimeZone(window_start, 'America/New_York')) BETWEEN toDate('2026-04-15') AND toDate('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 date
)
SELECT p.date AS date,
       c.contract_label AS contract_label,
       p.premium_usd AS premium_usd,
       round(greatest(s.stock_close - c.strike_usd, 0), 2) AS intrinsic_usd,
       round(p.premium_usd - greatest(s.stock_close - c.strike_usd, 0), 2) AS time_value_usd
FROM premium AS p
INNER JOIN spot AS s ON p.date = s.date
CROSS JOIN contract AS c
ORDER BY date