מהו מכרז הפתיחה? איך מניות נפתחות

SELECT
    ticker,
    round(toFloat64(maxIf(size, has(conditions, 17))) / 1e3, 1) AS auction_shares_k,
    toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 17))) AS auction_price,
    formatDateTime(toTimeZone(argMaxIf(sip_timestamp, (size, sip_timestamp), has(conditions, 17)), 'America/New_York'), '%H:%i:%S') AS auction_time_et,
    countIf(has(conditions, 17)) AS opening_print_count,
    countIf(has(conditions, 17) AND formatDateTime(toTimeZone(sip_timestamp, 'America/New_York'), '%H:%i:%S') = '09:30:00') AS open_prints_at_0930,
    minIf(size, has(conditions, 17)) AS smallest_open_print_shares,
    round(toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]) AND (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60 + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) >= 570 AND (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60 + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) < 575)) / 1e3) AS first5_shares_k,
    round(100 * toFloat64(maxIf(size, has(conditions, 17))) / toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]) AND (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60 + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) >= 570 AND (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60 + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) < 575)), 1) AS auction_pct_of_first5,
    round(100 * toFloat64(maxIf(size, has(conditions, 17))) / toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]))), 2) AS auction_pct_of_day,
    round(toFloat64(maxIf(size, has(conditions, 8))) / 1e6, 2) AS closing_auction_shares_m,
    round(toFloat64(maxIf(size, has(conditions, 8))) / toFloat64(maxIf(size, has(conditions, 17))), 1) AS close_vs_open_x
FROM global_markets.stocks_trades
WHERE ticker IN ('SPY', 'AAPL', 'NVDA')
  AND sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00'
GROUP BY ticker
ORDER BY indexOf(['SPY', 'AAPL', 'NVDA'], ticker)

WITH
    (
        SELECT countIf(window_start >= toDateTime('2026-07-02 09:30:00', 'America/New_York') AND window_start < toDateTime('2026-07-02 16:00:00', 'America/New_York'))
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= toDateTime('2026-07-02 00:00:00', 'America/New_York') AND window_start < toDateTime('2026-07-03 00:00:00', 'America/New_York')
    ) AS bars_jul02,
    (
        SELECT argMax(sip_timestamp, (size, sip_timestamp))
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL' AND sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00' AND has(conditions, 17)
    ) AS auction_ts,
    (
        SELECT argMax(exchange, (size, sip_timestamp))
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL' AND sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00' AND has(conditions, 17)
    ) AS listing_exchange
SELECT
    bars_jul02 AS spy_session_bars_jul02,
    countIf(has(conditions, 8) AND sip_timestamp < '2026-07-02 00:00:00') AS jul01_close_print_count,
    toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8) AND sip_timestamp < '2026-07-02 00:00:00')) AS prior_close_price,
    countIf(has(conditions, 17) AND sip_timestamp >= '2026-07-02 00:00:00') AS opening_print_count,
    toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 17) AND sip_timestamp >= '2026-07-02 00:00:00')) AS auction_price,
    formatDateTime(toTimeZone(auction_ts, 'America/New_York'), '%H:%i:%S.%f') AS auction_et,
    round(100 * (toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 17) AND sip_timestamp >= '2026-07-02 00:00:00')) - toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8) AND sip_timestamp < '2026-07-02 00:00:00'))) / toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8) AND sip_timestamp < '2026-07-02 00:00:00')), 2) AS overnight_gap_pct,
    formatDateTime(toTimeZone(minIf(sip_timestamp, sip_timestamp >= '2026-07-02 00:00:00' AND NOT hasAny(conditions, [15, 16, 38])), 'America/New_York'), '%H:%i:%S') AS first_trade_et,
    toFloat64(argMinIf(price, (sip_timestamp, sequence_number), sip_timestamp >= '2026-07-02 00:00:00' AND NOT hasAny(conditions, [15, 16, 38]))) AS first_trade_price,
    countIf(sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < auction_ts AND NOT hasAny(conditions, [15, 16, 38])) AS trades_before_official_open,
    countIf(sip_timestamp >= toDateTime('2026-07-02 09:30:00', 'America/New_York') AND sip_timestamp < auction_ts AND NOT hasAny(conditions, [15, 16, 38])) AS bell_to_cross_trades,
    countIf(has(conditions, 16) AND sip_timestamp >= '2026-07-02 00:00:00') AS official_open_msgs,
    countIf(has(conditions, 16) AND sip_timestamp >= '2026-07-02 00:00:00' AND exchange = listing_exchange) AS listing_venue_open_msgs,
    round(toFloat64(anyIf(price, has(conditions, 16) AND sip_timestamp >= '2026-07-02 00:00:00' AND exchange = listing_exchange)) - toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 17) AND sip_timestamp >= '2026-07-02 00:00:00')), 4) AS listing_open_msg_price_difference
FROM global_markets.stocks_trades
WHERE ticker = 'AAPL' AND sip_timestamp >= '2026-07-01 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00'

WITH auctions AS (
    SELECT ticker, toFloat64(argMax(price, (size, sip_timestamp))) AS official_open
    FROM global_markets.stocks_trades
    WHERE ticker IN ('SPY', 'AAPL', 'NVDA')
      AND sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00'
      AND has(conditions, 17)
    GROUP BY ticker
)
SELECT
    b.ticker AS ticker,
    toFloat64(anyIf(b.open, b.window_start = toDateTime('2026-07-02 09:30:00', 'America/New_York'))) AS bar_0930_open,
    a.official_open AS official_open,
    round(100 * (toFloat64(anyIf(b.open, b.window_start = toDateTime('2026-07-02 09:30:00', 'America/New_York'))) - a.official_open), 0) AS bar_open_minus_official_cents,
    round(sumIf(b.volume, b.window_start = toDateTime('2026-07-02 09:30:00', 'America/New_York')) / 1e3) AS bar_0930_vol_k,
    anyIf(b.transactions, b.window_start = toDateTime('2026-07-02 09:30:00', 'America/New_York')) AS bar_0930_trades,
    round(sumIf(b.volume, b.window_start = toDateTime('2026-07-02 12:30:00', 'America/New_York')) / 1e3) AS bar_1230_vol_k,
    round(sumIf(b.volume, b.window_start = toDateTime('2026-07-02 09:30:00', 'America/New_York')) / sumIf(b.volume, b.window_start = toDateTime('2026-07-02 12:30:00', 'America/New_York')), 1) AS x_0930_vs_1230
FROM global_markets.delayed_stocks_minute_aggs AS b
INNER JOIN auctions AS a ON a.ticker = b.ticker
WHERE b.ticker IN ('SPY', 'AAPL', 'NVDA')
  AND b.window_start >= toDateTime('2026-07-02 00:00:00', 'America/New_York')
  AND b.window_start < toDateTime('2026-07-03 00:00:00', 'America/New_York')
GROUP BY b.ticker, a.official_open
ORDER BY indexOf(['SPY', 'AAPL', 'NVDA'], b.ticker)

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 15 MINUTE), '%H:%i') AS et_time,
    round(sum(volume) / 1e6, 2) AS shares_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'AAPL'
  AND window_start >= toDateTime('2026-07-02 00:00:00', 'America/New_York')
  AND window_start < toDateTime('2026-07-03 00:00:00', 'America/New_York')
GROUP BY et_time
ORDER BY et_time

SELECT
    name,
    id,
    toUInt8(JSONExtractBool(update_rules, 'consolidated', 'updates_volume')) AS counts_toward_volume
FROM global_markets.stocks_condition_codes
WHERE asset_class = 'stocks' AND type = 'sale_condition' AND id IN (8, 15, 16, 17, 38)
ORDER BY id