Sessions from listing to the first close below the first print: SPCX vs four recent IPOs

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

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