SPCX: The Week It Went Underwater

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-07-01 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
GROUP BY month
ORDER BY month

WITH
    (
        SELECT argMax(toFloat64(close), window_start) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX' AND window_start >= toDateTime('2026-07-02 00:00:00') AND window_start < toDateTime('2026-07-03 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ) AS pw_close,
    (
        SELECT min(toFloat64(low)) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX' AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
    ) AS lo,
    (
        SELECT max(toFloat64(high)) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX' AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
    ) AS hi,
    (
        SELECT min(c) FROM (
            SELECT argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) 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-03 00:00:00')
            GROUP BY toDate(toTimeZone(window_start, 'America/New_York'))
        )
    ) AS prior_low_close,
    (
        SELECT countIf(c < 150) FROM (
            SELECT argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) AS c
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPCX' AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
            GROUP BY toDate(toTimeZone(window_start, 'America/New_York'))
        )
    ) AS closes_under_150
SELECT
    round(pw_close, 2) AS prior_week_close,
    round(toFloat64(argMaxIf(close, window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199)), 2) AS week_close,
    round((toFloat64(argMaxIf(close, window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199)) / pw_close - 1) * 100, 1) AS week_change_pct,
    round(hi, 2) AS week_high,
    round(lo, 2) AS week_low,
    formatDateTime(toTimeZone(minIf(window_start, toFloat64(low) <= lo + 0.011), 'America/New_York'), '%Y-%m-%d %H:%i') AS week_low_bar_et,
    countIf(toFloat64(close) < 150 AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) AS rth_minutes_below_150,
    150 AS underwater_threshold_usd,
    closes_under_150 AS closes_below_150,
    round(prior_low_close, 2) AS prior_closing_low,
    round(prior_low_close - toFloat64(argMaxIf(close, window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199)), 2) AS new_low_margin_usd,
    round(sumIf(toFloat64(close) * toFloat64(volume), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) / 1e9, 1) AS rth_dollar_bn,
    round(toFloat64(sum(volume)) / 1e6, 1) AS week_shares_m,
    uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS session_days_observed
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')

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-07-02 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
        GROUP BY et_date
    )
)
WHERE et_date >= toDate('2026-07-06')
ORDER BY et_date

WITH
    (
        SELECT (arrayElement(splitByString('. ', argMin(title, published_utc)), 1), toString(min(toDate(toTimeZone(published_utc, 'America/New_York')))))
        FROM global_markets.stocks_news
        WHERE has(tickers, 'SPCX') AND published_utc >= toDateTime('2026-07-06 04:00:00') AND published_utc < toDateTime('2026-07-11 04:00:00')
          AND title ILIKE '%quiet period%'
    ) AS quiet,
    (
        SELECT (arrayElement(splitByString('. ', argMin(title, published_utc)), 1), toString(min(toDate(toTimeZone(published_utc, 'America/New_York')))))
        FROM global_markets.stocks_news
        WHERE has(tickers, 'SPCX') AND published_utc >= toDateTime('2026-07-06 04:00:00') AND published_utc < toDateTime('2026-07-11 04:00:00')
          AND title ILIKE '%lost%revenue%'
    ) AS results,
    (
        SELECT (arrayElement(splitByString('. ', argMin(title, published_utc)), 1), toString(min(toDate(toTimeZone(published_utc, 'America/New_York')))))
        FROM global_markets.stocks_news
        WHERE has(tickers, 'SPCX') AND published_utc >= toDateTime('2026-07-06 04:00:00') AND published_utc < toDateTime('2026-07-11 04:00:00')
          AND title ILIKE '%borrowed%'
    ) AS borrow
SELECT
    count() AS week_articles,
    uniqExact(JSONExtractString(publisher, 'name')) AS publishers,
    countIf(title ILIKE '%nasdaq-100%') AS index_add_headlines,
    quiet.1 AS quiet_period_headline,
    quiet.2 AS quiet_period_date,
    results.1 AS results_headline,
    results.2 AS results_date,
    borrow.1 AS borrowing_headline,
    borrow.2 AS borrowing_date
FROM global_markets.stocks_news
WHERE has(tickers, 'SPCX')
  AND published_utc >= toDateTime('2026-07-06 04:00:00')
  AND published_utc < toDateTime('2026-07-11 04:00:00')

SELECT
    toString(wk) AS period_start,
    round(ret, 1) AS week_return_pct,
    round(dollar_bn, 1) AS week_rth_dollar_bn,
    sessions
FROM (
    SELECT toStartOfWeek(toDate(toTimeZone(window_start, 'America/New_York')), 1) AS wk,
           uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS sessions,
           (argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) / argMinIf(toFloat64(open), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) - 1) * 100 AS ret,
           sumIf(toFloat64(close) * toFloat64(volume), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) / 1e9 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-11 00:00:00')
    GROUP BY wk
)
ORDER BY period_start

SELECT
    b.ticker AS ticker,
    toString(any(i.ld)) AS listed,
    round(any(i.ipx), 2) AS issue_px,
    round(argMinIf(toFloat64(b.open), b.window_start, rth), 2) AS first_trade_open,
    round(argMaxIf(toFloat64(b.close), b.window_start, rth), 2) AS day28_close,
    round((argMaxIf(toFloat64(b.close), b.window_start, rth) / argMinIf(toFloat64(b.open), b.window_start, rth) - 1) * 100, 1) AS open_to_day28_pct,
    round((argMaxIf(toFloat64(b.close), b.window_start, rth) / any(i.ipx) - 1) * 100, 1) AS issue_to_day28_pct,
    toUInt8(b.ticker = 'SPCX') AS is_spcx
FROM global_markets.delayed_stocks_minute_aggs AS b
INNER JOIN (
    SELECT ticker, max(listing_date) AS ld, argMax(toFloat64(final_issue_price), listing_date) AS ipx
    FROM global_markets.stocks_ipos
    WHERE ticker IN ('SPCX', 'CRWV', 'CBRS', 'KLAR', 'VG', 'MDLN', 'LINE')
    GROUP BY ticker
) AS i ON b.ticker = i.ticker
WHERE b.ticker IN ('SPCX', 'CRWV', 'CBRS', 'KLAR', 'VG', 'MDLN', 'LINE')
  AND b.window_start >= toDateTime('2024-07-25 00:00:00')
  AND b.window_start < toDateTime('2026-07-11 00:00:00')
  AND toDate(toTimeZone(b.window_start, 'America/New_York')) >= i.ld
  AND toDate(toTimeZone(b.window_start, 'America/New_York')) <= i.ld + 28
  AND ((toHour(toTimeZone(b.window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(b.window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS rth
GROUP BY b.ticker
ORDER BY open_to_day28_pct

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-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 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 sum(toFloat64(volume) * toFloat64(close))
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPCX'
      AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
) AS spcx_d
SELECT
    countIf(d > spcx_d AND ticker != 'SPCX') + 1 AS spcx_rank,
    round(spcx_d / 1e9, 1) AS spcx_dollar_bn,
    round(100 * spcx_d / max(d), 1) AS pct_of_leader
FROM (
    SELECT ticker, sum(toFloat64(volume) * toFloat64(close)) AS d
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    GROUP BY ticker
)

WITH
    (
        SELECT (round(count() / 1e6, 2),
                round(100.0 * countIf(bid_price > 0 AND ask_price > 0 AND ask_price > bid_price) / count(), 2))
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'SPCX'
          AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 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(100.0 * countIf(size < 100) / count(), 1) AS odd_lot_pct_of_prints,
    quote_census.1 AS nbbo_updates_m,
    quote_census.2 AS clean_two_sided_pct
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)

SELECT
    session,
    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,
    count() AS quote_updates
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'SPCX'
  AND sip_timestamp >= toDateTime64('2026-07-06 13:30:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
  AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
GROUP BY toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session
ORDER BY session

WITH
    (
        SELECT concat('$', toString(round(toFloat64(toUInt32OrZero(substring(ticker, 14, 8))) / 1000, 2)),
               if(substring(ticker, 13, 1) = 'P', ' put', ' call'),
               ', expiry 20', substring(ticker, 7, 2), '-', substring(ticker, 9, 2), '-', substring(ticker, 11, 2))
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
        GROUP BY ticker ORDER BY sum(size) DESC LIMIT 1
    ) AS busiest_name
SELECT
    round(sum(size) / 1e6, 2) AS contracts_traded_m,
    round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 2) AS week_put_call_ratio,
    round(sum(toFloat64(price) * size) * 100 / 1e9, 2) AS premium_notional_busd,
    busiest_name AS busiest_contract
FROM global_markets.options_trades
WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
  AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)

SELECT
    concat('$', toString(toUInt32(bucket))) AS strike_bucket,
    toUInt64(sumIf(size, substring(ticker, 13, 1) = 'C')) AS call_contracts,
    toUInt64(sumIf(size, substring(ticker, 13, 1) = 'P')) AS put_contracts,
    round(100.0 * sumIf(size, substring(ticker, 13, 1) = 'P') / sum(size), 1) AS put_share_pct,
    round(100.0 * sumIf(size, substring(ticker, 7, 6) <= '260717') / sum(size), 1) AS expiring_by_jul17_pct,
    round(100 * sum(size) / max(sum(size)) OVER (), 1) AS pct_of_biggest_bucket
FROM global_markets.options_trades
WHERE startsWith(ticker, 'O:SPCX') AND length(ticker) = 21
  AND toUInt32OrZero(substring(ticker, 14, 8)) > 0
  AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
GROUP BY least(greatest(floor(toFloat64(toUInt32OrZero(substring(ticker, 14, 8))) / 1000 / 20) * 20, 80), 240) AS bucket
ORDER BY bucket

SELECT toString(date) AS d,
       round(toFloat64(any(short_volume)) / 1e6, 1) AS short_shares_m,
       round(toFloat64(any(total_volume)) / 1e6, 1) 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-07-06') AND date <= toDate('2026-07-10')
GROUP BY date
ORDER BY date

SELECT toString(settlement_date) AS settlement,
       round(toFloat64(max(short_interest)) / 1e6, 1) AS shares_short_m,
       round(toFloat64(max(avg_daily_volume)) / 1e6, 1) AS avg_daily_volume_m,
       max(days_to_cover) AS vendor_days_to_cover,
       round(toFloat64(max(short_interest)) / toFloat64(max(avg_daily_volume)), 2) AS implied_days_to_cover
FROM global_markets.stocks_short_interest
WHERE ticker = 'SPCX' AND settlement_date >= toDate('2026-06-01') AND settlement_date <= toDate('2026-06-30')
GROUP BY settlement_date
ORDER BY settlement_date