SPCX: ירידת מניית SpaceX מהשיא

WITH
    peak_stats AS (
        SELECT
            round(toFloat64(max(high)), 2) AS peak_hi,
            toDate(toTimeZone(argMax(window_start, (toFloat64(high), -toInt64(toUnixTimestamp(window_start)))), 'America/New_York')) AS peak_d
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE 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
    ),
    ipo_open 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(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ),
    issue_price AS (
        SELECT toFloat64(final_issue_price) AS issue_px
        FROM global_markets.stocks_ipos
        WHERE ticker = 'SPCX'
        ORDER BY listing_date DESC LIMIT 1
    ),
    jul7 AS (
        SELECT
            round(toFloat64(argMax(close, window_start)), 2) AS jul7_c,
            round(toFloat64(min(low)), 2) AS jul7_lo,
            argMin(window_start, (toFloat64(low), toInt64(toUnixTimestamp(window_start)))) AS low_bar
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= '2026-07-07 00:00:00' AND window_start < '2026-07-08 00:00:00'
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ),
    spy_ret AS (
        SELECT round((toFloat64(argMax(close, window_start)) / toFloat64(argMin(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 < '2026-07-08 00:00:00'
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    )
SELECT
    toString(round(ip.issue_px, 2)) AS issue_price,
    io.ipo_open_px AS ipo_open,
    ps.peak_hi AS peak_price,
    toString(ps.peak_d) AS peak_date,
    toMonth(ps.peak_d) AS peak_month,
    toDayOfMonth(ps.peak_d) AS peak_day,
    j.jul7_c AS jul7_close,
    j.jul7_lo AS jul7_low,
    formatDateTime(toTimeZone(j.low_bar, 'America/New_York'), '%H:%i') AS jul7_low_et,
    toHour(toTimeZone(j.low_bar, 'America/New_York')) * 60 + toMinute(toTimeZone(j.low_bar, 'America/New_York')) AS jul7_low_et_minute,
    round(io.ipo_open_px - j.jul7_c, 2) AS close_below_ipo_usd,
    round(io.ipo_open_px - j.jul7_lo, 2) AS low_below_ipo_usd,
    round((ps.peak_hi - io.ipo_open_px) / io.ipo_open_px * 100, 1) AS peak_above_ipo_pct,
    round((ps.peak_hi - j.jul7_c) / ps.peak_hi * 100, 1) AS decline_from_peak_pct,
    sr.spy_pct AS spy_period_pct
FROM peak_stats ps, ipo_open io, issue_price ip, jul7 j, spy_ret sr

SELECT
    toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
    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 < '2026-07-08 00:00:00'
  AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
GROUP BY session_date
ORDER BY session_date

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, 1) AS vol_m
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 20:00:00'
GROUP BY et_time
ORDER BY et_time

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
    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 >= '2026-06-12 00:00:00' AND sip_timestamp < '2026-07-08 00:00:00'
  AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
GROUP BY session_date
ORDER BY session_date

SELECT
    concat('20', substring(ticker, 7, 2), '-', substring(ticker, 9, 2), '-', substring(ticker, 11, 2)) AS expiry_date,
    if(substring(ticker, 13, 1) = 'C', 'call', 'put') AS call_or_put,
    toUInt32OrZero(substring(ticker, 14, 8)) / 1000 AS strike,
    concat(toString(toUInt32OrZero(substring(ticker, 14, 8)) / 1000), '-strike ', if(substring(ticker, 13, 1) = 'C', 'call', 'put')) AS label,
    sum(size) AS volume,
    round(sum(toFloat64(size) * toFloat64(price)) / 1e6, 2) AS notional_m,
    if(substring(ticker, 7, 6) = '260710', 1, 0) AS expires_jul10
FROM global_markets.options_trades
WHERE ticker LIKE 'O:SPCX%'
  AND length(ticker) = 21
  AND sip_timestamp >= '2026-07-07 00:00:00' AND sip_timestamp < '2026-07-08 00:00:00'
GROUP BY expiry_date, call_or_put, strike, label, expires_jul10
ORDER BY volume DESC
LIMIT 10

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
    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 >= '2026-07-01 00:00:00' AND sip_timestamp < '2026-07-08 00:00:00'
GROUP BY session_date
ORDER BY session_date

SELECT
    toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
    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 >= '2026-07-01 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
  AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
GROUP BY session_date
ORDER BY session_date

SELECT
    a.session_date AS session_date,
    round(a.spx_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,
        (toFloat64(argMax(close, window_start)) / toFloat64(argMin(open, window_start)) - 1) * 100 AS spx_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2026-07-01 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 session_date
) a
INNER JOIN (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        (toFloat64(argMax(close, window_start)) / toFloat64(argMin(open, window_start)) - 1) * 100 AS spcx_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPCX'
      AND window_start >= '2026-07-01 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 session_date
) 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 < toDateTime('2026-07-08 04:00:00')
    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 < toDateTime('2026-07-08 04:00:00')
    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,
    countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-07-07')) AS jul7_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(avg(n), 1) FROM daily_counts WHERE d <= toDate('2026-06-18')) AS first_week_avg,
    (SELECT round(avg(n), 1) FROM daily_counts WHERE d >= toDate('2026-07-01')) AS final_week_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 < toDateTime('2026-07-08 04:00:00')

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,
    toMonth(maxIf(filing_date, form_type = '4')) AS form4_month,
    toDayOfMonth(maxIf(filing_date, form_type = '4')) AS form4_dom,
    toString(max(filing_date)) AS last_filing,
    toMonth(max(filing_date)) AS last_filing_month,
    toDayOfMonth(max(filing_date)) AS last_filing_dom,
    countIf(filing_date > toDate('2026-06-26')) AS filings_after_jun26
FROM global_markets.stocks_sec_edgar_index
WHERE ticker = 'SPCX'
  AND filing_date >= toDate('2026-06-12') AND filing_date <= toDate('2026-07-07')

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
    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,
    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