2,178 answered market questions

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,
           sumIf(toFloat64(volume), substring(ticker, length(ticker) - 8, 1) = 'C') AS calls,
           sumIf(toFloat64(volume), substring(ticker, length(ticker) - 8, 1) = 'P') AS puts
    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,
           round(sumIf(r.vol, c.rn = 1) / avgIf(r.vol, c.rn BETWEEN 2 AND 21), 1) AS vol_ratio,
           sumIf(r.calls, c.rn = 1) AS calls,
           sumIf(r.puts, c.rn = 1) AS puts,
           maxIf(toYYYYMMDD(c.d), c.rn = 1) AS session_id
    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 sumIf(r.vol, c.rn = 1) >= 25000
       AND avgIf(r.vol, c.rn BETWEEN 2 AND 21) >= 5000
       AND countIf(c.rn BETWEEN 2 AND 21) >= 18
),
board AS (
    SELECT root, vol_ratio, calls, puts, session_id
    FROM scored
    ORDER BY vol_ratio DESC, root ASC
    LIMIT 10
)
SELECT root AS ticker,
       round(calls / 1000, 1) AS call_volume_k,
       round(puts / 1000, 1) AS put_volume_k,
       round(100.0 * calls / (calls + puts), 0) AS call_share_pct,
       session_id
FROM board
WHERE calls + puts > 0
ORDER BY call_share_pct DESC, ticker ASC