What follows a heavy options session: next-session absolute move vs. the same names on an ordinary day

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH daily AS (
    SELECT underlying_symbol AS sym,
           date AS d,
           sum(volume) AS vol,
           max(underlying_close) AS px
    FROM global_markets.options_greeks
    WHERE date >= today() - 200
      AND underlying_close > 0
      AND underlying_symbol NOT IN ('SPCX')
      AND underlying_symbol NOT IN ('KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND underlying_symbol NOT IN (SELECT ticker FROM global_markets.stocks_splits
                                    WHERE execution_date BETWEEN today() - 230 AND today())
    GROUP BY sym, d
),
seq AS (
    SELECT sym, d, vol, px,
           avg(vol) OVER (PARTITION BY sym ORDER BY d ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS base,
           count() OVER (PARTITION BY sym ORDER BY d ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS base_n,
           any(px) OVER (PARTITION BY sym ORDER BY d ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_px,
           any(px) OVER (PARTITION BY sym ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_px
    FROM daily
),
moves AS (
    SELECT sym, vol, base, base_n,
           if(next_px > 0, 100 * abs(next_px / px - 1), -1) AS next_abs,
           if(next_px > 0, 100 * (next_px / px - 1), -999) AS next_signed,
           if(prev_px > 0, 100 * abs(px / prev_px - 1), -1) AS own_abs
    FROM seq
),
typical AS (
    SELECT sym, quantileExact(0.5)(own_abs) AS typ
    FROM moves
    WHERE own_abs >= 0
    GROUP BY sym
)
SELECT arrayElement(['5x or more', '3x to 5x', '2x to 3x', '1x to 2x', 'below 1x'], bk) AS rvol_bucket,
       count() AS event_count,
       round(quantileExact(0.5)(next_abs), 2) AS median_next_move_pct,
       round(quantileExact(0.5)(typ), 2) AS median_typical_move_pct,
       round(quantileExact(0.5)(next_abs) - quantileExact(0.5)(typ), 2) AS gap_pp,
       round(quantileExact(0.5)(next_signed), 2) AS median_next_signed_pct
FROM (
    SELECT m.sym AS sym,
           multiIf(m.vol / m.base >= 5, 1, m.vol / m.base >= 3, 2, m.vol / m.base >= 2, 3,
                   m.vol / m.base >= 1, 4, 5) AS bk,
           m.next_abs AS next_abs,
           m.next_signed AS next_signed,
           t.typ AS typ
    FROM moves m INNER JOIN typical t ON m.sym = t.sym
    WHERE m.base_n = 20 AND m.base >= 5000 AND m.vol >= 25000 AND m.next_abs >= 0
)
GROUP BY bk
ORDER BY bk ASC