TradegateとXetraの取引時間と価格の違い

SELECT
    formatDateTime(toStartOfInterval(if(has(conditions, 8),
                                        toTimeZone(sip_timestamp, 'America/New_York') - INTERVAL 10 MINUTE,
                                        toTimeZone(sip_timestamp, 'America/New_York')),
                                     INTERVAL 10 MINUTE), '%H:%i') AS et_time,
    round(sum(toFloat64(size)) / 1e6, 3) AS volume_millions
FROM global_markets.stocks_trades
WHERE ticker = 'SPY'
  AND sip_timestamp >= toDateTime('2026-06-10 08:00:00')
  AND sip_timestamp <  toDateTime('2026-06-11 00:00:00')
  AND NOT hasAny(conditions, [15, 16, 38])
GROUP BY et_time
ORDER BY et_time

WITH
    (
        SELECT sum(toFloat64(size))
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPY'
          AND sip_timestamp >= toDateTime('2026-06-10 08:00:00')
          AND sip_timestamp <  toDateTime('2026-06-11 00:00:00')
          AND NOT hasAny(conditions, [15, 16, 38])
    ) AS day_volume,
    (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
     + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) AS et_minutes
SELECT
    multiIf(has(conditions, 8), 'final 10 minutes and closing print',
            et_minutes < 570, 'premarket 04:00 to 09:30',
            et_minutes < 600, 'first 30 minutes',
            et_minutes < 950, 'midday 10:00 to 15:50',
            et_minutes < 960, 'final 10 minutes and closing print',
                              'after hours 16:00 to 20:00') AS phase,
    round(sum(toFloat64(size)) / 1e6, 2)              AS volume_millions,
    round(100 * sum(toFloat64(size)) / day_volume, 2) AS pct_of_day
FROM global_markets.stocks_trades
WHERE ticker = 'SPY'
  AND sip_timestamp >= toDateTime('2026-06-10 08:00:00')
  AND sip_timestamp <  toDateTime('2026-06-11 00:00:00')
  AND NOT hasAny(conditions, [15, 16, 38])
GROUP BY phase
ORDER BY min(et_minutes)

WITH
    (
        SELECT round(quantileDeterministic(0.5)(
                   10000 * (toFloat64(ask_price) - toFloat64(bid_price))
                   / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2),
                   toUInt64(sequence_number)), 2)
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= toDateTime('2026-06-10 13:30:00')
          AND sip_timestamp <  toDateTime('2026-06-10 20:00:00')
          AND bid_price > 0
          AND ask_price > bid_price
          AND sequence_number > 0
    ) AS regular_session_median
SELECT
    formatDateTime(toStartOfHour(toTimeZone(sip_timestamp, 'America/New_York')), '%H:%i') AS et_time,
    round(quantileDeterministic(0.5)(
        10000 * (toFloat64(ask_price) - toFloat64(bid_price))
        / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2),
        toUInt64(sequence_number)), 2) AS median_spread_bps,
    regular_session_median   AS regular_hours_median_bps,
    count()                  AS quote_count
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'AAPL'
  AND sip_timestamp >= toDateTime('2026-06-10 08:00:00')
  AND sip_timestamp <  toDateTime('2026-06-11 00:00:00')
  AND bid_price > 0
  AND ask_price > bid_price
  AND sequence_number > 0
GROUP BY et_time
ORDER BY et_time

SELECT
    t.symbol                                                                      AS symbol,
    t.post_close_volume_pct                                                       AS post_close_volume_pct,
    round(abs(100 * (b.last_evening_close / t.closing_print_price - 1)), 3)       AS post_close_move_pct
FROM
(
    SELECT
        ticker                                                                        AS symbol,
        round(100 * sumIf(toFloat64(size), NOT has(conditions, 8)
                          AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
                            + toMinute(toTimeZone(sip_timestamp, 'America/New_York')) >= 960)
                  / sum(toFloat64(size)), 2)                                          AS post_close_volume_pct,
        toFloat64(argMaxIf(price, size, has(conditions, 8)))                          AS closing_print_price
    FROM global_markets.stocks_trades
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'JNJ')
      AND sip_timestamp >= toDateTime('2026-06-10 08:00:00')
      AND sip_timestamp <  toDateTime('2026-06-11 00:00:00')
      AND NOT hasAny(conditions, [15, 16, 38])
    GROUP BY ticker
    HAVING countIf(has(conditions, 8)) > 0
) AS t
INNER JOIN
(
    SELECT
        ticker                                                                        AS symbol,
        toFloat64(argMaxIf(close, window_start,
                           toHour(toTimeZone(window_start, 'America/New_York')) * 60
                             + toMinute(toTimeZone(window_start, 'America/New_York')) >= 960)) AS last_evening_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'JNJ')
      AND window_start >= toDateTime('2026-06-10 08:00:00')
      AND window_start <  toDateTime('2026-06-11 00:00:00')
    GROUP BY ticker
    HAVING countIf(toHour(toTimeZone(window_start, 'America/New_York')) * 60
                     + toMinute(toTimeZone(window_start, 'America/New_York')) >= 960) > 0
) AS b ON b.symbol = t.symbol
ORDER BY post_close_volume_pct DESC