옵션 거래는 몇 시에 종료되는가

SELECT
    et_time,
    round(100 * spy_contracts / sum(spy_contracts) OVER (), 2) AS spy_pct,
    round(100 * spx_contracts / sum(spx_contracts) OVER (), 2) AS spx_pct
FROM
(
    SELECT
        concat(toString(intDiv(945 + grid.slot, 60)), ':',
               leftPad(toString(modulo(945 + grid.slot, 60)), 2, '0')) AS et_time,
        ifNull(tape.spy_contracts, 0)                                  AS spy_contracts,
        ifNull(tape.spx_contracts, 0)                                  AS spx_contracts
    FROM
    (
        SELECT arrayJoin(range(41)) AS slot
    ) AS grid
    LEFT JOIN
    (
        WITH
            extract(ticker, '^O:([A-Z]+)')                AS root,
            toTimeZone(sip_timestamp, 'America/New_York') AS et
        SELECT
            toHour(et) * 60 + toMinute(et) - 945 AS slot,
            sumIf(size, root = 'SPY')            AS spy_contracts,
            sumIf(size, root IN ('SPX', 'SPXW')) AS spx_contracts
        FROM global_markets.options_trades
        WHERE (ticker LIKE 'O:SPY%' OR ticker LIKE 'O:SPX%')
          AND root IN ('SPY', 'SPX', 'SPXW')
          AND sip_timestamp >= '2026-07-13 19:45:00'
          AND sip_timestamp <  '2026-07-17 20:30:00'
          AND (toHour(et) * 60 + toMinute(et)) BETWEEN 945 AND 985
        GROUP BY slot
    ) AS tape ON tape.slot = grid.slot
)
ORDER BY et_time

SELECT
    session_date,
    spy_last_minute - 960 AS spy_minutes_after_4pm,
    spx_last_minute - 960 AS spx_minutes_after_4pm,
    concat(toString(intDiv(spy_last_minute, 60)), ':',
           leftPad(toString(modulo(spy_last_minute, 60)), 2, '0')) AS spy_last_print_et
FROM
(
    WITH
        extract(ticker, '^O:([A-Z]+)')                AS root,
        toTimeZone(sip_timestamp, 'America/New_York') AS et
    SELECT
        toString(toDate(et))                                          AS session_date,
        maxIf(toHour(et) * 60 + toMinute(et), root = 'SPY')            AS spy_last_minute,
        maxIf(toHour(et) * 60 + toMinute(et), root IN ('SPX', 'SPXW')) AS spx_last_minute
    FROM global_markets.options_trades
    WHERE (ticker LIKE 'O:SPY%' OR ticker LIKE 'O:SPX%')
      AND root IN ('SPY', 'SPX', 'SPXW')
      AND sip_timestamp >= '2026-07-01 20:00:00'
      AND sip_timestamp <  '2026-07-17 21:00:00'
      AND (toHour(et) * 60 + toMinute(et)) BETWEEN 960 AND 1020
    GROUP BY session_date
    HAVING countIf(root = 'SPY') > 0
       AND countIf(root IN ('SPX', 'SPXW')) > 0
)
ORDER BY session_date

SELECT
    underlying,
    round(100 * late_contracts / window_contracts, 2) AS pct_after_4pm,
    concat(toString(intDiv(last_minute, 60)), ':',
           leftPad(toString(modulo(last_minute, 60)), 2, '0')) AS last_print_et
FROM
(
    WITH
        extract(ticker, '^O:([A-Z]+)')                AS root,
        toTimeZone(sip_timestamp, 'America/New_York') AS et
    SELECT
        if(root = 'SPXW', 'SPX', root)                       AS underlying,
        sum(size)                                            AS window_contracts,
        sumIf(size, (toHour(et) * 60 + toMinute(et)) >= 960) AS late_contracts,
        max(toHour(et) * 60 + toMinute(et))                  AS last_minute
    FROM global_markets.options_trades
    WHERE (ticker LIKE 'O:AAPL%' OR ticker LIKE 'O:MSFT%' OR ticker LIKE 'O:NVDA%'
           OR ticker LIKE 'O:SPY%' OR ticker LIKE 'O:QQQ%' OR ticker LIKE 'O:IWM%'
           OR ticker LIKE 'O:SPX%')
      AND root IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'QQQ', 'IWM', 'SPX', 'SPXW')
      AND sip_timestamp >= '2026-07-15 19:45:00'
      AND sip_timestamp <  '2026-07-15 20:30:00'
      AND (toHour(et) * 60 + toMinute(et)) BETWEEN 945 AND 990
    GROUP BY underlying
)
ORDER BY pct_after_4pm DESC

SELECT
    concat(formatDateTime(session_day, '%b'), ' ', toString(toDayOfMonth(session_day)),
           ', ', toString(toYear(session_day)))                      AS session_label,
    last_minute - 780                                                AS minutes_after_1pm_close,
    concat(toString(intDiv(last_minute, 60)), ':',
           leftPad(toString(modulo(last_minute, 60)), 2, '0'))       AS last_option_print_et
FROM
(
    WITH
        extract(ticker, '^O:([A-Z]+)')                AS root,
        toTimeZone(sip_timestamp, 'America/New_York') AS et
    SELECT
        toDate(et)                          AS session_day,
        max(toHour(et) * 60 + toMinute(et)) AS last_minute
    FROM global_markets.options_trades
    WHERE ticker LIKE 'O:SPY%'
      AND root = 'SPY'
      AND (   (sip_timestamp >= '2024-07-03 16:30:00' AND sip_timestamp < '2024-07-03 19:00:00')
           OR (sip_timestamp >= '2024-11-29 17:30:00' AND sip_timestamp < '2024-11-29 20:00:00')
           OR (sip_timestamp >= '2024-12-24 17:30:00' AND sip_timestamp < '2024-12-24 20:00:00')
           OR (sip_timestamp >= '2025-07-03 16:30:00' AND sip_timestamp < '2025-07-03 19:00:00')
           OR (sip_timestamp >= '2025-11-28 17:30:00' AND sip_timestamp < '2025-11-28 20:00:00')
           OR (sip_timestamp >= '2025-12-24 17:30:00' AND sip_timestamp < '2025-12-24 20:00:00'))
    GROUP BY session_day
)
ORDER BY session_day