Unusual options activity: last completed session vs. each underlying's own 20-session average

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 tape AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(volume)) AS vol
    FROM global_markets.options_minute_aggs
    WHERE window_start >= toDateTime(today() - 45, 'America/New_York')
    GROUP BY d
),
ranked AS (
    SELECT d, vol, row_number() OVER (ORDER BY d DESC) AS raw_rn
    FROM tape
),
cal AS (
    SELECT d, vol, rn, sum(if(rn BETWEEN 2 AND 21, 1, 0)) OVER () AS baseline_sessions
    FROM (
        SELECT d, vol, row_number() OVER (ORDER BY d DESC) AS rn
        FROM ranked
        WHERE vol >= 0.75 * (SELECT quantileExact(0.5)(vol) FROM ranked WHERE raw_rn > 1)
    )
),
day_root AS (
    SELECT substring(ticker, 3, length(ticker) - 17) AS root,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(volume)) AS vol
    FROM global_markets.options_minute_aggs
    WHERE window_start >= toDateTime(today() - 45, 'America/New_York')
    GROUP BY root, d
),
scored AS (
    SELECT r.root AS root,
           sumIf(r.vol, c.rn = 1) AS last_vol,
           avgIf(r.vol, c.rn BETWEEN 2 AND 21) AS base_vol,
           maxIf(r.vol, c.rn BETWEEN 2 AND 21) AS prior_high,
           countIf(c.rn BETWEEN 2 AND 21) AS root_sessions,
           max(c.baseline_sessions) AS baseline_session_count,
           maxIf(toYYYYMMDD(c.d), c.rn = 1) AS session_id,
           maxIf(formatDateTime(c.d, '%b %e'), c.rn = 1) AS session_label
    FROM day_root r INNER JOIN cal c ON r.d = c.d
    WHERE c.rn <= 21
      AND r.root NOT IN ('SPCX')
      AND r.root 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')
    GROUP BY r.root
    HAVING last_vol >= 25000 AND base_vol >= 5000 AND root_sessions >= 18
)
SELECT root AS ticker,
       round(last_vol / base_vol, 1) AS vol_ratio,
       round(last_vol / 1000, 1) AS session_volume_k,
       round(base_vol / 1000, 1) AS baseline_volume_k,
       round(prior_high / 1000, 1) AS prior_high_volume_k,
       session_label,
       session_id,
       baseline_session_count
FROM scored
ORDER BY vol_ratio DESC, ticker ASC
LIMIT 10