The arc on one row: issue price, first print, June peak, and the latest close

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
    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
    ),
    spy_sessions AS (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            argMax(toFloat64(close), window_start) AS c
        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
        GROUP BY d
        HAVING count() >= 150
    ),
    peak_window AS (
        SELECT
            anyIf(x.c, x.d = pk.peak_d) AS spcx_peak_close,
            anyIf(y.c, y.d = pk.peak_d) AS spy_peak_close,
            anyIf(y.c, y.d = lt.latest_d) AS spy_latest_close
        FROM spcx_sessions x, spy_sessions y, peak_row pk, latest_row lt
        WHERE x.d = y.d
    )
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,
    round((lt.latest_c / pw.spcx_peak_close - 1) * 100, 1) AS spcx_since_peak_session_pct,
    round((pw.spy_latest_close / pw.spy_peak_close - 1) * 100, 1) AS spy_since_peak_session_pct,
    round((pw.spy_latest_close / pw.spy_peak_close - 1) * 100 - (lt.latest_c / pw.spcx_peak_close - 1) * 100, 1) AS peak_window_gap_pp
FROM peak_row pk, first_print fp, issue_row ip, latest_row lt, spy_ret sr, peak_window pw