פעילות אופציות חריגה ביום המסחר האחרון

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

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

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)
    )
),
bars AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           toFloat64(volume) AS v,
           toDate(concat('20', substring(ticker, length(ticker) - 14, 2), '-',
                         substring(ticker, length(ticker) - 12, 2), '-',
                         substring(ticker, length(ticker) - 10, 2))) AS expiry
    FROM global_markets.options_minute_aggs
    WHERE window_start >= toDateTime(today() - 12, 'America/New_York')
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM cal WHERE rn = 1)
),
scored AS (
    SELECT multiIf(expiry - d <= 0, 1, expiry - d <= 7, 2, expiry - d <= 30, 3,
                   expiry - d <= 90, 4, expiry - d <= 365, 5, 6) AS bk,
           v, d
    FROM bars
)
SELECT arrayElement(['same day', '1 to 7 days', '8 to 30 days', '31 to 90 days', '91 to 365 days', 'over a year'], bk) AS dte_bucket,
       round(sum(v) / 1e6, 2) AS contracts_m,
       round(100.0 * sum(v) / sum(sum(v)) OVER (), 1) AS pct_of_volume,
       toYYYYMMDD(max(d)) AS session_id
FROM scored
GROUP BY bk
ORDER BY bk ASC

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)
    )
),
w AS (
    SELECT d, vol,
           toStartOfMonth(d) + toIntervalDay(((5 - toDayOfWeek(toStartOfMonth(d)) + 7) % 7) + 14) AS third_friday
    FROM cal
    WHERE rn <= 25
),
marked AS (
    SELECT d, vol,
           (d = max(if(d <= third_friday, d, toDate('1970-01-01'))) OVER (PARTITION BY toStartOfMonth(d)))
             AND (third_friday <= max(d) OVER ()) AS is_expiry
    FROM w
),
latest AS (
    SELECT d, vol, is_expiry,
           max(if(is_expiry, d, toDate('1970-01-01'))) OVER () AS last_expiry_d
    FROM marked
)
SELECT formatDateTime(d, '%b %e') AS session,
       round(vol / 1e6, 1) AS contracts_m,
       multiIf(is_expiry, 'monthly expiration', 'ordinary') AS session_type,
       round(max(if(d = last_expiry_d, vol, 0)) OVER () / 1e6, 1) AS monthly_expiry_m,
       toYYYYMMDD(d) AS session_id
FROM latest
ORDER BY d ASC

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