SpaceX Nasdaq-100 मध्ये सामील: इंडेक्स-ॲड ट्रेड

WITH
    (
        SELECT (toString(listing_date), issuer_name)
        FROM global_markets.stocks_ipos
        WHERE ticker = 'SPCX'
        ORDER BY listing_date DESC LIMIT 1
    ) AS ipo
SELECT
    formatDateTime(toStartOfMonth(window_start), '%Y-%m') AS month,
    count() AS minute_bars,
    round(min(toFloat64(low)), 2) AS low_usd,
    round(max(toFloat64(high)), 2) AS high_usd,
    ipo.1 AS spacex_listing_date,
    ipo.2 AS issuer
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= '2026-01-01 00:00:00' AND window_start < '2026-07-01 00:00:00'
GROUP BY month
ORDER BY month

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session,
    round(toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]))) / 1e6, 1) AS day_matched_shares_m,
    round(toFloat64(maxIf(size, has(conditions, 8))) / 1e6, 2) AS auction_shares_m,
    round(100 * toFloat64(maxIf(size, has(conditions, 8))) / toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]))), 1) AS auction_pct_of_day,
    round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))), 2) AS official_close,
    countIf(has(conditions, 8)) AS code8_prints,
    formatDateTime(toTimeZone(argMaxIf(sip_timestamp, (size, sip_timestamp), has(conditions, 8)), 'America/New_York'), '%H:%i:%S') AS auction_time_et
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND ((sip_timestamp >= '2026-07-01 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00')
    OR (sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'))
GROUP BY session
ORDER BY session

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session,
    round(toFloat64(maxIf(size, has(conditions, 8))) / 1e6, 2) AS auction_shares_m,
    round(100 * toFloat64(maxIf(size, has(conditions, 8))) / toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]))), 1) AS auction_pct_of_day,
    round(toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]))) / 1e6, 1) AS day_matched_shares_m,
    round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))), 2) AS official_close,
    countIf(has(conditions, 8)) AS code8_prints
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND sip_timestamp >= '2026-06-12 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
GROUP BY session
ORDER BY session

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 5 MINUTE), '%H:%i') AS et_time,
    round(toFloat64(argMax(close, window_start)), 2) AS price_usd,
    round(toFloat64(sum(volume)) / 1e6, 2) AS shares_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
GROUP BY et_time
ORDER BY et_time

WITH
    (
        SELECT (
            round(toFloat64(argMin(open, window_start)), 2),
            round(toFloat64(argMaxIf(close, window_start, window_start < '2026-07-06 17:01:00')), 2),
            round(toFloat64(argMaxIf(close, window_start, window_start < '2026-07-06 19:31:00')), 2),
            round(toFloat64(minIf(low, window_start = '2026-07-06 19:30:00')), 2),
            round(toFloat64(maxIf(high, window_start = '2026-07-06 19:30:00')), 2),
            round(toFloat64(sumIf(volume, window_start >= '2026-07-06 19:30:00')) / 1e6, 1)
        )
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
    ) AS bars,
    (
        SELECT round(quantileDeterministic(0.5)(auction_m, day_key), 2)
        FROM (
            SELECT
                toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
                toUInt64(toYYYYMMDD(toDate(toTimeZone(sip_timestamp, 'America/New_York')))) AS day_key,
                toFloat64(maxIf(size, has(conditions, 8))) / 1e6 AS auction_m
            FROM global_markets.stocks_trades
            WHERE ticker = 'SPCX'
              AND sip_timestamp >= '2026-06-12 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00'
            GROUP BY d, day_key
        )
    ) AS med_prior,
    (
        SELECT round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))), 2)
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPCX'
          AND sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00'
    ) AS spcx_prev_close,
    (
        SELECT (
            round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8) AND sip_timestamp < '2026-07-03 00:00:00')), 2),
            round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8) AND sip_timestamp >= '2026-07-06 00:00:00')), 2)
        )
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPY'
          AND ((sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00')
            OR (sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'))
    ) AS spy,
    (
        SELECT maxIf(sip_timestamp, has(conditions, 8))
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPCX' AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
    ) AS auction_ts
SELECT
    bars.1 AS open_0930,
    bars.2 AS px_1300_et,
    bars.3 AS px_1530_close,
    bars.4 AS px_1530_bar_low,
    bars.5 AS px_1530_bar_high,
    bars.6 AS shares_1530_1559_m,
    round(toFloat64(argMaxIf(price, (sip_timestamp, sequence_number), sip_timestamp < auction_ts AND NOT has(conditions, 8) AND NOT hasAny(conditions, [15, 16, 38]))), 2) AS last_continuous_px,
    formatDateTime(toTimeZone(maxIf(sip_timestamp, sip_timestamp < auction_ts AND NOT has(conditions, 8) AND NOT hasAny(conditions, [15, 16, 38])), 'America/New_York'), '%H:%i:%S') AS last_continuous_et,
    countIf(has(conditions, 8)) AS code8_prints,
    round(toFloat64(maxIf(size, has(conditions, 8))) / 1e6, 2) AS auction_shares_m,
    round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))), 2) AS auction_price,
    formatDateTime(toTimeZone(auction_ts, 'America/New_York'), '%H:%i:%S') AS auction_time_et,
    round(toFloat64(maxIf(size, has(conditions, 8))) * toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))) / 1e9, 2) AS auction_notional_busd,
    round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))) - bars.3, 2) AS close_minus_1530_usd,
    round(bars.1 - toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))), 2) AS open_minus_close_usd,
    round(bars.2 - toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))), 2) AS px1300_minus_close_usd,
    round(bars.1 - bars.2, 2) AS open_minus_1300_usd,
    round((toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))) - toFloat64(argMaxIf(price, (sip_timestamp, sequence_number), sip_timestamp < auction_ts AND NOT has(conditions, 8) AND NOT hasAny(conditions, [15, 16, 38])))) * 100, 1) AS cross_vs_last_tick_cents,
    round((1 - toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))) / spcx_prev_close) * 100, 2) AS spcx_close_below_prior_pct,
    spy.2 AS spy_close_jul6,
    round((spy.2 / spy.1 - 1) * 100, 2) AS spy_day_change_pct,
    med_prior AS median_prior_auction_m,
    round(toFloat64(maxIf(size, has(conditions, 8))) / 1e6 / med_prior, 1) AS auction_vs_prior_median_x
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'

SELECT
    multiIf(has(conditions, 8), 'Closing auction (one print)', exchange = 4, 'Off-exchange (TRF)', 'On-exchange, continuous') AS bucket,
    round(toFloat64(sum(size)) / 1e6, 1) AS shares_m,
    round(100 * toFloat64(sum(size)) / sum(toFloat64(sum(size))) OVER (), 1) AS pct_of_matched,
    count() AS prints
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
  AND NOT hasAny(conditions, [15, 16, 38])
GROUP BY bucket
ORDER BY bucket

WITH
    (
        SELECT round(toFloat64(argMaxIf(price, (size, sip_timestamp), has(conditions, 8))), 2)
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPCX'
          AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
    ) AS monday_close
SELECT
    count() AS morning_bars,
    formatDateTime(toTimeZone(max(window_start), 'America/New_York'), '%H:%i') AS last_bar_et,
    round(toFloat64(argMin(open, window_start)), 2) AS open_0930,
    round((1 - toFloat64(argMin(open, window_start)) / monday_close) * 100, 1) AS open_below_monday_close_pct,
    round(min(toFloat64(low)), 2) AS morning_low,
    round(max(toFloat64(high)), 2) AS morning_high,
    round(toFloat64(argMax(close, window_start)), 2) AS px_1259_et,
    round((1 - toFloat64(argMax(close, window_start)) / monday_close) * 100, 1) AS px_1259_below_monday_close_pct,
    round(toFloat64(sum(volume)) / 1e6, 1) AS morning_shares_m,
    monday_close AS monday_official_close
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= '2026-07-07 13:30:00' AND window_start < '2026-07-07 17:00:00'