SPCX: SpaceX上場初月の株価と売買代金

WITH
    (
        SELECT toFloat64(final_issue_price)
        FROM global_markets.stocks_ipos
        WHERE ticker = 'SPCX'
        ORDER BY listing_date DESC LIMIT 1
    ) AS issue_px,
    (
        SELECT count() FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= toDateTime('2026-05-01 00:00:00') AND window_start < toDateTime('2026-06-01 00:00:00')
    ) AS may_bars,
    (
        SELECT count() FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime('2026-06-19 00:00:00') AND window_start < toDateTime('2026-06-20 00:00:00')
    ) AS spy_jun19,
    (
        SELECT (toString(argMax(et_date, c)), max(c), argMax(c, et_date))
        FROM (
            SELECT
                toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
                argMax(toFloat64(close), window_start) AS c
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPCX'
              AND window_start >= toDateTime('2026-06-12 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
              AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
            GROUP BY et_date
        )
    ) AS closes,
    (
        SELECT max(toFloat64(high)) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= toDateTime('2026-06-12 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
    ) AS hi,
    (
        SELECT min(toFloat64(low)) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= toDateTime('2026-06-12 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
    ) AS lo
SELECT
    round(issue_px, 2) AS issue_price_usd,
    round(toFloat64(argMin(open, window_start)), 2) AS opening_cross_price,
    round((toFloat64(argMin(open, window_start)) / issue_px - 1) * 100, 1) AS open_pop_vs_issue_pct,
    closes.1 AS peak_close_date,
    round(closes.2, 2) AS peak_close,
    round(closes.3, 2) AS final_close,
    round((closes.3 / issue_px - 1) * 100, 1) AS final_vs_issue_pct,
    round((1 - closes.3 / closes.2) * 100, 1) AS final_below_peak_pct,
    round(hi, 2) AS month_high,
    formatDateTime(toTimeZone(minIf(window_start, toFloat64(high) >= hi - 0.011), 'America/New_York'), '%Y-%m-%d %H:%i') AS month_high_first_bar_et,
    countIf(toFloat64(high) >= hi - 0.011) AS bars_within_cent_of_high,
    argMinIf(transactions, window_start, toFloat64(high) >= hi - 0.011) AS high_minute_trades,
    round(lo, 2) AS month_low_extended,
    formatDateTime(toTimeZone(argMin(window_start, toFloat64(low)), 'America/New_York'), '%Y-%m-%d %H:%i') AS month_low_bar_et,
    countIf(toFloat64(low) <= lo + 0.011) AS bars_within_cent_of_low,
    argMin(transactions, toFloat64(low)) AS low_minute_trades,
    round(minIf(toFloat64(low), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199), 2) AS month_low_regular_hours,
    round(minIf(toFloat64(low), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) - lo, 2) AS rth_minus_extended_low,
    round(toFloat64(sum(volume)) / 1e9, 2) AS month_shares_bn,
    uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS sessions,
    may_bars AS may_2026_bars,
    spy_jun19 AS spy_bars_june19
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= toDateTime('2026-06-12 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')

WITH
    (
        SELECT (toString(listing_date), toFloat64(final_issue_price), round(toFloat64(total_offer_size) / 1e9, 1),
                primary_exchange, issuer_name, security_description, toFloat64(max_shares_offered))
        FROM global_markets.stocks_ipos
        WHERE ticker = 'SPCX'
        ORDER BY listing_date DESC LIMIT 1
    ) AS ipo,
    (SELECT count() FROM global_markets.stocks_balance_sheets WHERE cik = '0001181412' OR has(tickers, 'SPCX')) AS bs_rows,
    (SELECT count() FROM global_markets.stocks_cash_flow_statements WHERE cik = '0001181412' OR has(tickers, 'SPCX')) AS cf_rows,
    (SELECT count() FROM global_markets.stocks_income_statements WHERE cik = '0001181412' OR has(tickers, 'SPCX')) AS is_rows
SELECT
    ipo.1 AS listing_date,
    ipo.5 AS issuer,
    round(ipo.2, 2) AS final_issue_price_usd,
    ipo.3 AS offer_size_busd,
    round(ipo.7 / 1e6, 1) AS max_shares_offered_m,
    ipo.4 AS listing_exchange,
    ipo.6 AS security,
    bs_rows + cf_rows + is_rows AS fundamentals_rows

SELECT
    toStartOfMonth(window_start) AS month,
    count() AS minute_bars,
    round(min(toFloat64(low)), 2) AS low_usd,
    round(max(toFloat64(high)), 2) AS high_usd,
    round(toFloat64(sum(volume)) / 1e6, 2) AS shares_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= toDateTime('2025-06-01 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
GROUP BY month
ORDER BY month

SELECT
    form_type,
    uniqExact(accession_number) AS filings,
    toString(min(filing_date)) AS first_filed,
    toString(max(filing_date)) AS last_filed
FROM global_markets.stocks_sec_edgar_index
WHERE cik = '0001181412'
  AND filing_date >= toDate('2026-01-01') AND filing_date <= toDate('2026-06-30')
GROUP BY form_type
ORDER BY min(filing_date), form_type

SELECT
    toString(filing_date) AS filed,
    form_type,
    replaceAll(substring(items_text, 1, 170), '\n', ' · ') AS opening_lines
FROM global_markets.stocks_8k_text
WHERE cik = '0001181412'
  AND filing_date >= toDate('2026-06-01') AND filing_date <= toDate('2026-06-30')
ORDER BY filing_date

WITH
    (
        SELECT toFloat64(final_issue_price)
        FROM global_markets.stocks_ipos
        WHERE ticker = 'SPCX'
        ORDER BY listing_date DESC LIMIT 1
    ) AS issue_px,
    (
        SELECT (formatDateTime(toTimeZone(min(sip_timestamp), 'America/New_York'), '%H:%i:%S'), round(count() / 1e6, 2))
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'SPCX'
          AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-06-13 00:00:00', 9)
    ) AS q,
    (
        SELECT (round(toFloat64(sum(volume)) / 1e6, 1), round(sum(toFloat64(close) * toFloat64(volume)) / 1e9, 1),
                round(max(toFloat64(high)), 2), round(min(toFloat64(low)), 2))
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= toDateTime('2026-06-12 00:00:00') AND window_start < toDateTime('2026-06-13 00:00:00')
    ) AS bars
SELECT
    q.1 AS first_quote_et,
    q.2 AS day_quote_updates_m,
    formatDateTime(toTimeZone(minIf(sip_timestamp, has(conditions, 17)), 'America/New_York'), '%H:%i:%S') AS opening_cross_et,
    round(toFloat64(maxIf(size, has(conditions, 17))) / 1e6, 2) AS opening_cross_shares_m,
    round(toFloat64(argMaxIf(price, size, has(conditions, 17))), 2) AS opening_cross_price,
    formatDateTime(toTimeZone(minIf(sip_timestamp, has(conditions, 8)), 'America/New_York'), '%H:%i:%S') AS closing_cross_et,
    round(toFloat64(maxIf(size, has(conditions, 8))) / 1e6, 2) AS closing_cross_shares_m,
    round(toFloat64(argMaxIf(price, size, has(conditions, 8))), 2) AS closing_cross_price,
    round((toFloat64(argMaxIf(price, size, has(conditions, 8))) / issue_px - 1) * 100, 1) AS close_vs_issue_pct,
    round(count() / 1e6, 2) AS day_prints_m,
    bars.1 AS day_shares_m,
    bars.2 AS day_dollar_bn,
    bars.3 AS day_high,
    bars.4 AS day_low
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-06-13 00:00:00', 9)

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_bucket,
    count() AS quote_updates,
    round(quantileDeterministicIf(0.5)(toFloat64(ask_price) - toFloat64(bid_price), toUInt64(toUnixTimestamp64Micro(sip_timestamp)), bid_price > 0 AND ask_price >= bid_price) * 100, 1) AS med_spread_cents,
    round(quantileDeterministicIf(0.5)((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, toUInt64(toUnixTimestamp64Micro(sip_timestamp)), bid_price > 0 AND ask_price >= bid_price), 2) AS med_spread_bps
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'SPCX'
  AND sip_timestamp >= toDateTime64('2026-06-12 13:30:00', 9) AND sip_timestamp < toDateTime64('2026-06-12 20:00:00', 9)
GROUP BY et_bucket
ORDER BY et_bucket

SELECT
    et_date,
    close_usd,
    round(if(prev_close = 0, NULL, (close_usd / prev_close - 1) * 100), 1) AS change_pct,
    shares_m,
    dollar_bn
FROM (
    SELECT et_date, close_usd, shares_m, dollar_bn,
           lagInFrame(close_usd) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
            round(argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199), 2) AS close_usd,
            round(toFloat64(sum(volume)) / 1e6, 1) AS shares_m,
            round(sum(toFloat64(close) * toFloat64(volume)) / 1e9, 2) AS dollar_bn
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= toDateTime('2026-06-12 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
        GROUP BY et_date
    )
)
ORDER BY et_date

SELECT
    ticker,
    round(sum(toFloat64(volume) * toFloat64(close)) / 1e9, 1) AS regular_hours_dollar_bn,
    round(100 * sum(toFloat64(volume) * toFloat64(close)) / max(sum(toFloat64(volume) * toFloat64(close))) OVER (), 1) AS pct_of_leader,
    toUInt8(ticker = 'SPCX') AS is_spcx
FROM global_markets.delayed_stocks_minute_aggs
WHERE window_start >= toDateTime('2026-06-12 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
  AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
GROUP BY ticker
ORDER BY regular_hours_dollar_bn DESC
LIMIT 12

WITH
    (
        SELECT (round(count() / 1e6, 2),
                round(100.0 * countIf(bid_price > 0 AND ask_price > 0 AND ask_price > bid_price) / count(), 2),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price = bid_price),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price < bid_price),
                countIf(bid_price <= 0 OR ask_price <= 0),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price < bid_price AND sip_timestamp < toDateTime64('2026-06-13 00:00:00', 9)),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price < bid_price AND sip_timestamp >= toDateTime64('2026-06-30 00:00:00', 9)))
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'SPCX'
          AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    ) AS quote_census
SELECT
    round(count() / 1e6, 2) AS prints_m,
    quantileDeterministic(0.5)(toFloat64(size), toUInt64(abs(sequence_number))) AS median_print_shares,
    round(avg(toFloat64(size)), 1) AS avg_print_shares,
    round(100.0 * countIf(size < 100) / count(), 1) AS odd_lot_pct_of_prints,
    round(100.0 * countIf(toFloat64(size) != round(toFloat64(size))) / count(), 2) AS fractional_pct_of_prints,
    quote_census.1 AS nbbo_updates_m,
    quote_census.2 AS clean_two_sided_pct,
    quote_census.3 AS locked_updates,
    quote_census.4 AS crossed_updates,
    quote_census.5 AS one_sided_or_empty_updates,
    quote_census.6 AS crossed_first_session,
    quote_census.7 AS crossed_final_session
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)

SELECT
    session,
    quote_updates,
    invalid_dropped,
    med_spread_cents,
    tw_spread_cents,
    med_spread_bps,
    tw_spread_bps,
    round(100 * (tw_spread_bps / min(tw_spread_bps) OVER () - 1), 0) AS pct_above_tightest_session
FROM (
    SELECT
        session,
        count() AS quote_updates,
        countIf(NOT valid) AS invalid_dropped,
        round(quantileDeterministicIf(0.5)(spread, toUInt64(ts_us), valid) * 100, 1) AS med_spread_cents,
        round(sumIf(spread * dt, valid AND dt > 0) / sumIf(dt, valid AND dt > 0) * 100, 1) AS tw_spread_cents,
        round(quantileDeterministicIf(0.5)(spread / mid * 10000, toUInt64(ts_us), valid), 2) AS med_spread_bps,
        round(sumIf(spread / mid * dt, valid AND dt > 0) / sumIf(dt, valid AND dt > 0) * 10000, 2) AS tw_spread_bps
    FROM (
        SELECT session, spread, mid, valid, ts_us,
               greatest(if(next_us = 0, end_us, least(next_us, end_us)) - ts_us, 0) AS dt
        FROM (
            SELECT
                toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session,
                toFloat64(ask_price) - toFloat64(bid_price) AS spread,
                (toFloat64(ask_price) + toFloat64(bid_price)) / 2 AS mid,
                (bid_price > 0 AND ask_price > 0 AND ask_price >= bid_price) AS valid,
                toUnixTimestamp64Micro(sip_timestamp) AS ts_us,
                toUnixTimestamp64Micro(toDateTime64(concat(toString(toDate(toTimeZone(sip_timestamp, 'America/New_York'))), ' 20:00:00'), 6, 'UTC')) AS end_us,
                leadInFrame(toUnixTimestamp64Micro(sip_timestamp)) OVER (PARTITION BY toDate(toTimeZone(sip_timestamp, 'America/New_York')) ORDER BY sip_timestamp ASC, sequence_number ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_us
            FROM global_markets.cache_stocks_quotes
            WHERE ticker = 'SPCX'
              AND sip_timestamp >= toDateTime64('2026-06-12 15:46:00', 9)
              AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
              AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
        )
    )
    GROUP BY session
)
ORDER BY session

SELECT
    ticker,
    count() AS quote_updates,
    countIf(NOT valid) AS invalid_dropped,
    round(quantileDeterministicIf(0.5)(spread, toUInt64(ts_us), valid) * 100, 1) AS med_spread_cents,
    round(quantileDeterministicIf(0.5)(spread / mid * 10000, toUInt64(ts_us), valid), 2) AS med_spread_bps,
    round(sumIf(spread / mid * dt, valid AND dt > 0) / sumIf(dt, valid AND dt > 0) * 10000, 2) AS tw_spread_bps
FROM (
    SELECT ticker, spread, mid, valid, ts_us,
           greatest(if(next_us = 0, end_us, least(next_us, end_us)) - ts_us, 0) AS dt
    FROM (
        SELECT
            ticker,
            toFloat64(ask_price) - toFloat64(bid_price) AS spread,
            (toFloat64(ask_price) + toFloat64(bid_price)) / 2 AS mid,
            (bid_price > 0 AND ask_price > 0 AND ask_price >= bid_price) AS valid,
            toUnixTimestamp64Micro(sip_timestamp) AS ts_us,
            toUnixTimestamp64Micro(toDateTime64('2026-06-30 20:00:00', 6, 'UTC')) AS end_us,
            leadInFrame(toUnixTimestamp64Micro(sip_timestamp)) OVER (PARTITION BY ticker ORDER BY sip_timestamp ASC, sequence_number ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_us
        FROM global_markets.cache_stocks_quotes
        WHERE ticker IN ('SPCX', 'AAPL', 'NATH')
          AND sip_timestamp >= toDateTime64('2026-06-30 13:30:00', 9)
          AND sip_timestamp < toDateTime64('2026-06-30 20:00:00', 9)
    )
)
GROUP BY ticker
ORDER BY ticker

WITH
    (
        SELECT (round(sum(toFloat64(price) * size) * 100 / 1e9, 2), round(sum(size) / 1e6, 1))
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:AAPL') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    ) AS aapl,
    (
        SELECT (
            concat('$', toString(intDiv(toUInt32OrZero(substring(ticker, 14, 8)), 1000)),
                   if(substring(ticker, 13, 1) = 'P', ' put', ' call'),
                   ', expiry 20', substring(ticker, 7, 2), '-', substring(ticker, 9, 2), '-', substring(ticker, 11, 2)),
            toUInt64(sum(size)), toUInt64(count()),
            round(toFloat64(sum(toFloat64(price) * size)) / toFloat64(sum(size)), 2),
            round(sum(toFloat64(price) * size) * 100 / 1e6, 1))
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
        GROUP BY ticker ORDER BY sum(size) DESC LIMIT 1
    ) AS busiest,
    (
        SELECT (
            concat('$', toString(intDiv(toUInt32OrZero(substring(ticker, 14, 8)), 1000)),
                   if(substring(ticker, 13, 1) = 'P', ' put', ' call'),
                   ', expiry 20', substring(ticker, 7, 2), '-', substring(ticker, 9, 2), '-', substring(ticker, 11, 2)),
            round(toFloat64(sum(toFloat64(price) * size)) / toFloat64(sum(size)), 2),
            round(sum(toFloat64(price) * size) * 100 / 1e6, 1))
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
        GROUP BY ticker ORDER BY sum(toFloat64(price) * size) DESC LIMIT 1
    ) AS premium_magnet,
    (
        SELECT max(pc)
        FROM (
            SELECT round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 3) AS pc
            FROM global_markets.options_trades
            WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
              AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
            GROUP BY toDate(sip_timestamp)
        )
    ) AS max_daily_pc
SELECT
    formatDateTime(toTimeZone(min(sip_timestamp), 'America/New_York'), '%Y-%m-%d %H:%i:%S') AS first_print_et,
    uniqExact(toDate(sip_timestamp)) AS option_sessions,
    round(count() / 1e6, 2) AS prints_m,
    uniqExact(ticker) AS distinct_contracts,
    round(sum(size) / 1e6, 2) AS contracts_traded_m,
    round(sum(toFloat64(price) * size) * 100 / 1e9, 2) AS premium_notional_busd,
    round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 2) AS month_put_call_ratio,
    max_daily_pc AS max_session_put_call_ratio,
    uniqExact(substring(ticker, 7, 6)) AS expiries_traded,
    concat('20', substring(max(substring(ticker, 7, 6)), 1, 2), '-', substring(max(substring(ticker, 7, 6)), 3, 2), '-', substring(max(substring(ticker, 7, 6)), 5, 2)) AS longest_expiry,
    round(100 * toFloat64(sumIf(size, substring(ticker, 7, 6) = '260618')) / toFloat64(sum(size)), 1) AS jun18_expiry_share_pct,
    busiest.1 AS busiest_contract,
    busiest.2 AS busiest_contract_volume,
    busiest.3 AS busiest_contract_prints,
    busiest.4 AS busiest_contract_avg_premium,
    busiest.5 AS busiest_contract_notional_musd,
    premium_magnet.1 AS top_premium_contract,
    premium_magnet.2 AS top_premium_avg,
    premium_magnet.3 AS top_premium_notional_musd,
    aapl.1 AS aapl_full_june_notional_busd,
    aapl.2 AS aapl_full_june_contracts_m,
    round(round(sum(toFloat64(price) * size) * 100 / 1e9, 2) - aapl.1, 2) AS spcx_minus_aapl_notional_busd
FROM global_markets.options_trades
WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
  AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)

SELECT
    toDate(sip_timestamp) AS session,
    count() AS prints,
    toUInt64(sum(size)) AS contracts_traded,
    toUInt64(sumIf(size, substring(ticker, 13, 1) = 'C')) AS call_contracts,
    toUInt64(sumIf(size, substring(ticker, 13, 1) = 'P')) AS put_contracts,
    round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 2) AS put_call_ratio,
    round(100 * toFloat64(sum(size)) / max(toFloat64(sum(size))) OVER (), 1) AS pct_of_busiest_session
FROM global_markets.options_trades
WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
  AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
GROUP BY session
ORDER BY session

SELECT
    concat('$', toString(toUInt32(bucket))) AS strike_bucket,
    call_contracts,
    put_contracts,
    round(100.0 * put_contracts / (call_contracts + put_contracts), 1) AS put_share_pct,
    round(100 * (call_contracts + put_contracts) / max(call_contracts + put_contracts) OVER (), 1) AS pct_of_biggest_bucket
FROM (
    SELECT
        least(floor(toFloat64(toUInt32OrZero(substring(ticker, 14, 8))) / 1000 / 25) * 25, 450) AS bucket,
        toUInt64(sumIf(size, substring(ticker, 13, 1) = 'C')) AS call_contracts,
        toUInt64(sumIf(size, substring(ticker, 13, 1) = 'P')) AS put_contracts
    FROM global_markets.options_trades
    WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
      AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    GROUP BY bucket
)
ORDER BY toUInt32OrZero(substring(strike_bucket, 2))

SELECT
    toDate(sip_timestamp) AS session,
    count() AS nbbo_updates,
    countIf(bid_price <= 0 OR ask_price < bid_price) AS dropped_bad_quotes,
    round(avgIf(toFloat64(ask_price) - toFloat64(bid_price), bid_price > 0 AND ask_price >= bid_price) * 100, 1) AS avg_spread_cents,
    round(quantileDeterministicIf(0.5)(toFloat64(ask_price) - toFloat64(bid_price), toUInt64(sequence_number), bid_price > 0 AND ask_price >= bid_price) * 100, 1) AS med_spread_cents,
    round(100 * avgIf((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2), bid_price > 0 AND ask_price >= bid_price), 2) AS avg_spread_pct_of_mid
FROM global_markets.cache_options_quotes
WHERE ticker = 'O:SPCX260618P00175000'
  AND sip_timestamp >= toDateTime64('2026-06-16 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-06-19 00:00:00', 9)
GROUP BY session
ORDER BY session

WITH
    (
        SELECT count()
        FROM global_markets.stocks_news
        WHERE has(tickers, 'SPCX')
          AND published_utc >= toDateTime('2025-06-01 00:00:00')
          AND published_utc < toDateTime('2026-06-01 00:00:00')
    ) AS prior_12mo,
    (
        SELECT (toString(d), n)
        FROM (
            SELECT toDate(toTimeZone(published_utc, 'America/New_York')) AS d, count() AS n
            FROM global_markets.stocks_news
            WHERE has(tickers, 'SPCX')
              AND published_utc >= toDateTime('2026-06-01 00:00:00')
              AND published_utc < toDateTime('2026-07-01 04:00:00')
            GROUP BY d ORDER BY n DESC, d ASC LIMIT 1
        )
    ) AS peak_day,
    (
        SELECT (JSONExtractString(any(publisher), 'name'), count())
        FROM global_markets.stocks_news
        WHERE has(tickers, 'SPCX')
          AND published_utc >= toDateTime('2026-06-01 00:00:00')
          AND published_utc < toDateTime('2026-07-01 04:00:00')
        GROUP BY JSONExtractString(publisher, 'name') ORDER BY count() DESC LIMIT 1
    ) AS top_pub,
    (
        SELECT n
        FROM (
            SELECT toDate(toTimeZone(published_utc, 'America/New_York')) AS d, count() AS n
            FROM global_markets.stocks_news
            WHERE has(tickers, 'SPCX')
              AND published_utc >= toDateTime('2026-06-01 00:00:00')
              AND published_utc < toDateTime('2026-07-01 04:00:00')
            GROUP BY d ORDER BY d ASC LIMIT 1
        )
    ) AS first_day_n,
    (
        SELECT substring(title, 1, 90)
        FROM global_markets.stocks_news
        WHERE has(tickers, 'SPCX')
          AND published_utc >= toDateTime('2026-06-22 04:00:00')
          AND published_utc < toDateTime('2026-06-24 04:00:00')
          AND title ILIKE '%bond%'
        ORDER BY published_utc ASC LIMIT 1
    ) AS notes_headline
SELECT
    prior_12mo AS prior_12_months_articles,
    toString(min(toDate(toTimeZone(published_utc, 'America/New_York')))) AS first_tagged_day,
    first_day_n AS first_day_articles,
    peak_day.1 AS peak_day_date,
    peak_day.2 AS peak_day_articles,
    count() AS june_articles,
    uniqExact(JSONExtractString(publisher, 'name')) AS publishers,
    top_pub.1 AS top_publisher,
    top_pub.2 AS top_publisher_articles,
    round(100.0 * top_pub.2 / count(), 0) AS top_publisher_pct,
    countIf(has(tickers, 'TSLA')) AS tsla_co_articles,
    countIf(has(tickers, 'NVDA')) AS nvda_co_articles,
    countIf(hasAny(tickers, ['GOOG', 'GOOGL', 'GOOGN', 'GOOGM'])) AS alphabet_co_articles,
    countIf(has(tickers, 'RKLB')) AS rklb_co_articles,
    countIf(has(tickers, 'ASTS')) AS asts_co_articles,
    countIf(has(tickers, 'TSLA')) - countIf(has(tickers, 'RKLB')) AS tsla_minus_rklb,
    notes_headline AS notes_offering_headline
FROM global_markets.stocks_news
WHERE has(tickers, 'SPCX')
  AND published_utc >= toDateTime('2026-06-01 00:00:00')
  AND published_utc < toDateTime('2026-07-01 04:00:00')

SELECT
    date,
    round(toFloat64(any(short_volume)) / 1e6, 2) AS short_shares_m,
    round(toFloat64(any(total_volume)) / 1e6, 2) AS offexchange_total_m,
    round(100 * toFloat64(any(short_volume)) / toFloat64(any(total_volume)), 1) AS short_pct_of_offexchange
FROM global_markets.stocks_short_volume
WHERE ticker = 'SPCX' AND date >= toDate('2026-06-01') AND date <= toDate('2026-06-30')
GROUP BY date
ORDER BY date

SELECT
    toString(minIf(settlement_date, settlement_date >= toDate('2026-06-01'))) AS new_entity_settlement,
    round(toFloat64(argMinIf(short_interest, settlement_date, settlement_date >= toDate('2026-06-01'))) / 1e6, 2) AS shares_short_m,
    round(toFloat64(argMinIf(avg_daily_volume, settlement_date, settlement_date >= toDate('2026-06-01'))) / 1e6, 2) AS avg_daily_volume_m,
    argMinIf(days_to_cover, settlement_date, settlement_date >= toDate('2026-06-01')) AS reported_days_to_cover,
    round(toFloat64(argMinIf(short_interest, settlement_date, settlement_date >= toDate('2026-06-01'))) / toFloat64(argMinIf(avg_daily_volume, settlement_date, settlement_date >= toDate('2026-06-01'))), 2) AS implied_days_to_cover,
    countIf(settlement_date > toDate('2026-06-15')) AS settlements_published_after_june15,
    toString(argMaxIf(settlement_date, settlement_date, settlement_date < toDate('2026-06-01'))) AS last_old_entity_settlement,
    argMaxIf(short_interest, settlement_date, settlement_date < toDate('2026-06-01')) AS last_old_entity_short_interest,
    round(toFloat64(argMinIf(short_interest, settlement_date, settlement_date >= toDate('2026-06-01'))) / toFloat64(argMaxIf(short_interest, settlement_date, settlement_date < toDate('2026-06-01'))), 0) AS jump_multiple_vs_old_entity
FROM global_markets.stocks_short_interest
WHERE ticker = 'SPCX'