3,256 answered market questions

WITH
reports AS (
    SELECT
        toString(ticker)    AS sym,
        toDate(filing_date) AS report_date
    FROM global_markets.stocks_8k_text
    WHERE ticker IN ('AAPL', 'AMD', 'AMZN', 'AVGO', 'DIS', 'GOOGL', 'JPM', 'KO', 'META', 'MSFT', 'NFLX', 'NVDA', 'ORCL', 'QCOM', 'TSLA', 'WMT')
      AND filing_date >= '2021-09-01'
      AND filing_date <  '2026-09-20'
      AND (items_text ILIKE '%results of operations and financial condition%'
        OR items_text ILIKE '%item 2.02%')
    GROUP BY sym, report_date
),
sessions AS (
    SELECT
        toString(ticker) AS sym,
        toDate(date)     AS session_date,
        toFloat64(close) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'AMD', 'AMZN', 'AVGO', 'DIS', 'GOOGL', 'JPM', 'KO', 'META', 'MSFT', 'NFLX', 'NVDA', 'ORCL', 'QCOM', 'TSLA', 'WMT')
      AND date >= '2021-08-01'
      AND date <  '2026-09-27'
      AND close > 0
),
spans AS (
    SELECT
        r.sym         AS sym,
        r.report_date AS report_date,
        maxIf(s.session_date, s.session_date < r.report_date) AS pre_date,
        minIf(s.session_date, s.session_date > r.report_date) AS post_date
    FROM reports AS r
    INNER JOIN sessions AS s ON s.sym = r.sym
    WHERE s.session_date >= r.report_date - 8
      AND s.session_date <= r.report_date + 8
    GROUP BY r.sym, r.report_date
    HAVING countIf(s.session_date < r.report_date) > 0
       AND countIf(s.session_date > r.report_date) > 0
),
moves AS (
    SELECT
        sp.sym         AS sym,
        sp.report_date AS report_date,
        sp.pre_date    AS pre_date,
        round(100 * abs(b.px / a.px - 1), 2) AS realized_pct
    FROM spans AS sp
    INNER JOIN sessions AS a ON a.sym = sp.sym AND a.session_date = sp.pre_date
    INNER JOIN sessions AS b ON b.sym = sp.sym AND b.session_date = sp.post_date
),
greeks AS (
    SELECT
        toString(underlying_symbol)   AS sym,
        toDate(date)                  AS pre_date,
        toDate(expiration_date)       AS expiry,
        lower(option_type)            AS side,
        toFloat64(strike_price)       AS strike,
        toFloat64(option_close)       AS opt_px,
        toFloat64(underlying_close)   AS spot,
        toFloat64(implied_volatility) AS iv,
        toUInt16(days_to_expiry)      AS dte
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('AAPL', 'AMD', 'AMZN', 'AVGO', 'DIS', 'GOOGL', 'JPM', 'KO', 'META', 'MSFT', 'NFLX', 'NVDA', 'ORCL', 'QCOM', 'TSLA', 'WMT')
      AND date >= '2021-09-01'
      AND date <  '2026-09-20'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 1 AND 45
      AND underlying_close > 0
      AND option_close > 0
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
),
chain AS (
    SELECT
        g.sym      AS sym,
        g.pre_date AS pre_date,
        g.expiry   AS expiry,
        g.side     AS side,
        g.strike   AS strike,
        g.opt_px   AS opt_px,
        g.spot     AS spot,
        g.iv       AS iv,
        g.dte      AS dte
    FROM greeks AS g
    INNER JOIN moves AS m ON m.sym = g.sym AND m.pre_date = g.pre_date
    WHERE g.expiry > m.report_date
),
front AS (
    SELECT sym, pre_date, min(expiry) AS expiry
    FROM chain
    GROUP BY sym, pre_date
),
straddles AS (
    SELECT
        c.sym       AS sym,
        c.pre_date  AS pre_date,
        c.strike    AS strike,
        any(c.spot) AS spot,
        max(c.dte)  AS dte,
        avgIf(c.opt_px, c.side IN ('call', 'c')) AS call_px,
        avgIf(c.opt_px, c.side IN ('put', 'p'))  AS put_px,
        avg(c.iv)   AS atm_iv
    FROM chain AS c
    INNER JOIN front AS f
        ON f.sym = c.sym AND f.pre_date = c.pre_date AND f.expiry = c.expiry
    GROUP BY c.sym, c.pre_date, c.strike
    HAVING countIf(c.side IN ('call', 'c')) > 0
       AND countIf(c.side IN ('put', 'p')) > 0
),
implied AS (
    SELECT
        sym,
        pre_date,
        argMin(round(100 * (call_px + put_px) / spot, 2), abs(strike / spot - 1)) AS straddle_pct,
        argMin(round(100 * atm_iv * sqrt(dte / 365), 2), abs(strike / spot - 1))  AS iv_root_t_pct
    FROM straddles
    GROUP BY sym, pre_date
)
SELECT
    m.sym                                               AS symbol,
    toUInt32(count())                                   AS event_count,
    toUInt32(countIf(m.realized_pct <= i.straddle_pct)) AS under_implied_count,
    round(quantileDeterministic(0.5)(i.straddle_pct, cityHash64(m.sym, m.report_date)), 2) AS median_implied_pct,
    round(quantileDeterministic(0.5)(m.realized_pct, cityHash64(m.sym, m.report_date)), 2) AS median_realized_pct,
    round(quantileDeterministic(0.5)(m.realized_pct / i.straddle_pct, cityHash64(m.sym, m.report_date)), 2) AS median_ratio,
    toString(min(m.report_date))                        AS sample_from,
    toString(max(m.report_date))                        AS sample_through
FROM moves AS m
INNER JOIN implied AS i ON i.sym = m.sym AND i.pre_date = m.pre_date
WHERE i.straddle_pct > 0
GROUP BY m.sym
HAVING count() >= 5
ORDER BY under_implied_count / event_count DESC, symbol ASC