SPCX: SpaceX Stock Price Decline From Peak

WITH
    spcx_sessions AS (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            argMax(toFloat64(close), window_start) AS c,
            max(toFloat64(high)) AS hi
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= '2026-06-12 00:00:00' AND window_start < now()
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
        GROUP BY d
        HAVING count() >= 150
    ),
    latest_row AS (
        SELECT max(d) AS latest_d, argMax(c, d) AS latest_c
        FROM spcx_sessions
    ),
    peak_row AS (
        SELECT max(hi) AS peak_hi, argMax(d, (hi, -toInt32(d))) AS peak_d
        FROM spcx_sessions
    ),
    first_print AS (
        SELECT round(toFloat64(argMin(open, window_start)), 2) AS ipo_open_px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= '2026-06-12 00:00:00' AND window_start < '2026-06-13 00:00:00'
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    ),
    issue_row AS (
        SELECT toFloat64(final_issue_price) AS issue_px
        FROM global_markets.stocks_ipos
        WHERE ticker = 'SPCX'
        ORDER BY listing_date DESC LIMIT 1
    ),
    spy_ret AS (
        SELECT round((argMax(toFloat64(close), window_start) / argMin(toFloat64(open), window_start) - 1) * 100, 2) AS spy_pct
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= '2026-06-12 00:00:00' AND window_start < now()
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    )
SELECT
    toString(round(ip.issue_px, 2)) AS issue_price,
    fp.ipo_open_px AS ipo_open,
    round(pk.peak_hi, 2) AS peak_price,
    formatDateTime(pk.peak_d, '%b %e, %Y') AS peak_label,
    toMonth(pk.peak_d) AS peak_month,
    toDayOfMonth(pk.peak_d) AS peak_day,
    formatDateTime(lt.latest_d, '%b %e, %Y') AS latest_label,
    round(lt.latest_c, 2) AS latest_close,
    round(pk.peak_hi - lt.latest_c, 2) AS drop_from_peak_usd,
    round((pk.peak_hi - lt.latest_c) / pk.peak_hi * 100, 1) AS decline_from_peak_pct,
    round((pk.peak_hi - fp.ipo_open_px) / fp.ipo_open_px * 100, 1) AS peak_above_ipo_pct,
    round((lt.latest_c / fp.ipo_open_px - 1) * 100, 1) AS vs_first_print_pct,
    round((lt.latest_c / ip.issue_px - 1) * 100, 1) AS vs_issue_pct,
    sr.spy_pct AS spy_period_pct,
    round(sr.spy_pct - (lt.latest_c / fp.ipo_open_px - 1) * 100, 1) AS spy_minus_spcx_pp
FROM peak_row pk, first_print fp, issue_row ip, latest_row lt, spy_ret sr

SELECT
    toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
    formatDateTime(toDate(toTimeZone(window_start, 'America/New_York')), '%b %e') AS session_label,
    round(toFloat64(argMin(open, window_start)), 2) AS rth_open,
    round(toFloat64(argMax(close, window_start)), 2) AS rth_close,
    round(toFloat64(min(low)), 2) AS rth_low,
    round(toFloat64(max(high)), 2) AS rth_high,
    round(toFloat64(sum(volume)) / 1e6, 1) AS vol_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= '2026-06-12 00:00:00' AND window_start < now()
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
GROUP BY session_date, session_label
HAVING count() >= 150
ORDER BY session_date

WITH latest_session AS (
    SELECT max(d) AS latest_d
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= '2026-06-12 00:00:00' AND window_start < now()
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
        GROUP BY d
        HAVING count() >= 150
    )
)
SELECT
    formatDateTime(toTimeZone(toStartOfInterval(window_start, INTERVAL 30 MINUTE), 'America/New_York'), '%H:%i') AS et_time,
    round(toFloat64(argMax(close, window_start)), 2) AS close_px,
    round(toFloat64(min(low)), 2) AS bucket_low,
    round(toFloat64(sum(volume)) / 1e6, 2) AS vol_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPCX'
  AND window_start >= '2026-06-12 00:00:00' AND window_start < now()
  AND toDate(toTimeZone(window_start, 'America/New_York')) = (SELECT latest_d FROM latest_session)
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
GROUP BY et_time
ORDER BY et_time

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
    formatDateTime(toDate(toTimeZone(sip_timestamp, 'America/New_York')), '%b %e') AS session_label,
    round(avgIf((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, bid_price > 0 AND ask_price > bid_price), 2) AS avg_spread_bps,
    round(avgIf((toFloat64(ask_size) + toFloat64(bid_size)) / 2, bid_price > 0 AND ask_price > bid_price), 0) AS avg_depth,
    countIf(NOT (bid_price > 0 AND ask_price > bid_price)) AS dropped_invalid
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'SPCX'
  AND sip_timestamp < now()
  AND ((sip_timestamp >= '2026-06-12 00:00:00' AND sip_timestamp < '2026-07-11 00:00:00')
       OR sip_timestamp >= now() - INTERVAL 21 DAY)
  AND (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60 + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) BETWEEN 570 AND 959
GROUP BY session_date, session_label
HAVING countIf(bid_price > 0 AND ask_price > bid_price) > 0
ORDER BY session_date

WITH latest_opt_session AS (
    SELECT max(toDate(toTimeZone(sip_timestamp, 'America/New_York'))) AS d
    FROM global_markets.options_trades
    WHERE ticker LIKE 'O:SPCX%'
      AND length(ticker) = 21
      AND sip_timestamp >= now() - INTERVAL 8 DAY AND sip_timestamp < now()
)
SELECT
    concat(toString(toUInt32OrZero(substring(ticker, 14, 8)) / 1000), '-strike ', if(substring(ticker, 13, 1) = 'C', 'call', 'put')) AS label,
    formatDateTime(toDate(concat('20', substring(ticker, 7, 2), '-', substring(ticker, 9, 2), '-', substring(ticker, 11, 2))), '%b %e, %Y') AS expiry_label,
    formatDateTime(max(toDate(toTimeZone(sip_timestamp, 'America/New_York'))), '%b %e, %Y') AS session_label,
    sum(size) AS volume,
    round(sum(toFloat64(size) * toFloat64(price)) / 1e6, 2) AS notional_m
FROM global_markets.options_trades
WHERE ticker LIKE 'O:SPCX%'
  AND length(ticker) = 21
  AND sip_timestamp >= now() - INTERVAL 8 DAY AND sip_timestamp < now()
  AND toDate(toTimeZone(sip_timestamp, 'America/New_York')) = (SELECT d FROM latest_opt_session)
GROUP BY label, expiry_label
ORDER BY volume DESC
LIMIT 10

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
    formatDateTime(toDate(toTimeZone(sip_timestamp, 'America/New_York')), '%b %e') AS session_label,
    round(sumIf(size, substring(ticker, 13, 1) = 'P') / sumIf(size, substring(ticker, 13, 1) = 'C'), 2) AS pc_ratio,
    sumIf(size, substring(ticker, 13, 1) = 'C') AS call_vol,
    sumIf(size, substring(ticker, 13, 1) = 'P') AS put_vol
FROM global_markets.options_trades
WHERE ticker LIKE 'O:SPCX%'
  AND length(ticker) = 21
  AND sip_timestamp >= now() - INTERVAL 21 DAY AND sip_timestamp < now()
GROUP BY session_date, session_label
HAVING sumIf(size, substring(ticker, 13, 1) = 'C') > 0
ORDER BY session_date

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
    formatDateTime(toDate(toTimeZone(sip_timestamp, 'America/New_York')), '%b %e') AS session_label,
    round(avgIf((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, bid_price > 0 AND ask_price > bid_price), 2) AS opt_spread_bps,
    round(avgIf((toFloat64(ask_size) + toFloat64(bid_size)) / 2, bid_price > 0 AND ask_price > bid_price), 0) AS avg_size,
    countIf(NOT (bid_price > 0 AND ask_price > bid_price)) AS dropped_invalid,
    count() AS quotes
FROM global_markets.cache_options_quotes
WHERE ticker LIKE 'O:SPCX%'
  AND sip_timestamp >= now() - INTERVAL 8 DAY AND sip_timestamp < now()
  AND (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60 + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) BETWEEN 570 AND 959
GROUP BY session_date, session_label
HAVING countIf(bid_price > 0 AND ask_price > bid_price) > 0
ORDER BY session_date

SELECT
    a.session_date AS session_date,
    a.session_label AS session_label,
    round(a.spy_pct, 2) AS spy_pct,
    round(b.spcx_pct, 2) AS spcx_pct
FROM (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        formatDateTime(toDate(toTimeZone(window_start, 'America/New_York')), '%b %e') AS session_label,
        (argMax(toFloat64(close), window_start) / argMin(toFloat64(open), window_start) - 1) * 100 AS spy_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= now() - INTERVAL 21 DAY AND window_start < now()
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY session_date, session_label
    HAVING count() >= 150
) a
INNER JOIN (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        (argMax(toFloat64(close), window_start) / argMin(toFloat64(open), window_start) - 1) * 100 AS spcx_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPCX'
      AND window_start >= '2026-06-12 00:00:00'
      AND window_start >= now() - INTERVAL 21 DAY AND window_start < now()
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY session_date
    HAVING count() >= 150
) b ON a.session_date = b.session_date
ORDER BY a.session_date

WITH daily_counts AS (
    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-12 04:00:00') AND published_utc < now()
    GROUP BY d
),
top_pub AS (
    SELECT JSONExtractString(publisher, 'name') AS p, count() AS n
    FROM global_markets.stocks_news
    WHERE has(tickers, 'SPCX')
      AND published_utc >= toDateTime('2026-06-12 04:00:00') AND published_utc < now()
    GROUP BY p ORDER BY n DESC, p ASC LIMIT 1
)
SELECT
    count() AS total_articles,
    uniqExact(JSONExtractString(publisher, 'name')) AS publishers,
    (SELECT p FROM top_pub) AS top_publisher,
    round(100.0 * (SELECT n FROM top_pub) / count(), 1) AS top_publisher_share_pct,
    (SELECT toMonth(argMax(d, (n, -toInt32(toDayOfYear(d))))) FROM daily_counts) AS busiest_month,
    (SELECT toDayOfMonth(argMax(d, (n, -toInt32(toDayOfYear(d))))) FROM daily_counts) AS busiest_dom,
    countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-06-12')) AS ipo_day_articles,
    countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-06-16')) AS peak_session_articles,
    (SELECT max(n) FROM daily_counts WHERE d > toDate('2026-06-16')) AS max_daily_after_peak,
    countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-06-12')) - (SELECT max(n) FROM daily_counts WHERE d > toDate('2026-06-16')) AS ipo_day_minus_max_after,
    (SELECT round(sum(n) / 7, 1) FROM daily_counts WHERE d <= toDate('2026-06-18')) AS first_week_avg,
    (SELECT round(sum(n) / 7, 1) FROM daily_counts WHERE d > today() - 7) AS last7_avg,
    countIf(has(tickers, 'TSLA')) AS tsla_co_tagged,
    countIf(positionCaseInsensitive(title, 'nasdaq') > 0) AS nasdaq_titled
FROM global_markets.stocks_news
WHERE has(tickers, 'SPCX')
  AND published_utc >= toDateTime('2026-06-12 04:00:00') AND published_utc < now()

SELECT
    count() AS total_filings,
    countIf(form_type = '4') AS form4_filings,
    countIf(form_type = '3') AS form3_filings,
    countIf(form_type = '8-K') AS eightk_filings,
    countIf(form_type NOT IN ('4', '3', '8-K')) AS other_filings,
    formatDateTime(minIf(filing_date, form_type = '4'), '%b %e, %Y') AS first_form4_label,
    toMonth(minIf(filing_date, form_type = '4')) AS first_form4_month,
    toDayOfMonth(minIf(filing_date, form_type = '4')) AS first_form4_dom,
    formatDateTime(max(filing_date), '%b %e, %Y') AS last_filing_label,
    toInt32(today() - max(filing_date)) AS days_since_last_filing,
    countIf(filing_date > toDate('2026-06-16')) AS filings_since_peak
FROM global_markets.stocks_sec_edgar_index
WHERE ticker = 'SPCX'
  AND filing_date >= toDate('2026-06-12') AND filing_date <= today()

WITH daily AS (
    SELECT ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        argMin(toFloat64(open), window_start) AS rth_open,
        argMax(toFloat64(close), window_start) AS rth_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ((ticker = 'CRWV' AND window_start >= '2025-03-28 00:00:00' AND window_start < '2025-04-23 00:00:00')
        OR (ticker = 'KLAR' AND window_start >= '2025-09-10 00:00:00' AND window_start < '2025-10-06 00:00:00')
        OR (ticker = 'MDLN' AND window_start >= '2025-12-17 00:00:00' AND window_start < '2026-01-12 00:00:00')
        OR (ticker = 'CBRS' AND window_start >= '2026-05-14 00:00:00' AND window_start < '2026-06-09 00:00:00')
        OR (ticker = 'SPCX' AND window_start >= '2026-06-12 00:00:00' AND window_start < '2026-07-08 00:00:00'))
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    GROUP BY ticker, session_date
),
w AS (
    SELECT ticker, session_date, rth_close,
        row_number() OVER (PARTITION BY ticker ORDER BY session_date) AS rn,
        first_value(rth_open) OVER (PARTITION BY ticker ORDER BY session_date) AS d1_open
    FROM daily
),
agg AS (
    SELECT ticker,
        min(session_date) AS first_session,
        any(d1_open) AS day1_open,
        count() AS sessions_in_window,
        countIf(rth_close < d1_open) AS closes_below,
        minIf(toInt32(rn), rth_close < d1_open) AS break_rn,
        minIf(session_date, rth_close < d1_open) AS break_d
    FROM w GROUP BY ticker
)
SELECT a.ticker AS ticker,
    toString(a.first_session) AS listed,
    round(l.issue_px, 2) AS issue_price,
    round(a.day1_open, 2) AS first_rth_open,
    round((a.day1_open / l.issue_px - 1) * 100, 1) AS open_vs_issue_pct,
    round(abs((a.day1_open / l.issue_px - 1) * 100), 1) AS open_vs_issue_pct_abs,
    a.sessions_in_window AS sessions_in_window,
    a.closes_below AS closes_below_first_print,
    if(a.closes_below > 0, toString(a.break_rn), 'never in window') AS first_break,
    if(a.closes_below > 0, formatDateTime(a.break_d, '%b %e, %Y'), 'never in window') AS break_label,
    a.break_rn AS break_session_n
FROM agg a
INNER JOIN (
    SELECT ticker, min(listing_date) AS ld, argMin(toFloat64(final_issue_price), listing_date) AS issue_px
    FROM global_markets.stocks_ipos
    WHERE ticker IN ('SPCX', 'CBRS', 'MDLN', 'CRWV', 'KLAR') AND listing_date >= '2025-01-01' AND ipo_status = 'history'
    GROUP BY ticker
) l ON a.ticker = l.ticker
ORDER BY a.first_session