The month on one row: issue to close, with the extremes and their receipts

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
    (
        SELECT toFloat64(final_issue_price)
        FROM global_markets.stocks_ipos
        WHERE ticker = 'SPCX'
        ORDER BY listing_date DESC LIMIT 1
    ) AS issue_px,
    (
        SELECT count() FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPCX'
          AND window_start >= toDateTime('2026-05-01 00:00:00') AND window_start < toDateTime('2026-06-01 00:00:00')
    ) AS may_bars,
    (
        SELECT count() FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime('2026-06-19 00:00:00') AND window_start < toDateTime('2026-06-20 00:00:00')
    ) AS spy_jun19,
    (
        SELECT (toString(argMax(et_date, c)), max(c), argMax(c, et_date))
        FROM (
            SELECT
                toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
                argMax(toFloat64(close), window_start) 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-01 00:00:00')
              AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
            GROUP BY et_date
        )
    ) AS closes,
    (
        SELECT max(toFloat64(high)) 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-01 00:00:00')
    ) AS hi,
    (
        SELECT min(toFloat64(low)) 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-01 00:00:00')
    ) AS lo
SELECT
    round(issue_px, 2) AS issue_price_usd,
    round(toFloat64(argMin(open, window_start)), 2) AS opening_cross_price,
    round((toFloat64(argMin(open, window_start)) / issue_px - 1) * 100, 1) AS open_pop_vs_issue_pct,
    closes.1 AS peak_close_date,
    round(closes.2, 2) AS peak_close,
    round(closes.3, 2) AS final_close,
    round((closes.3 / issue_px - 1) * 100, 1) AS final_vs_issue_pct,
    round((1 - closes.3 / closes.2) * 100, 1) AS final_below_peak_pct,
    round(hi, 2) AS month_high,
    formatDateTime(toTimeZone(minIf(window_start, toFloat64(high) >= hi - 0.011), 'America/New_York'), '%Y-%m-%d %H:%i') AS month_high_first_bar_et,
    countIf(toFloat64(high) >= hi - 0.011) AS bars_within_cent_of_high,
    argMinIf(transactions, window_start, toFloat64(high) >= hi - 0.011) AS high_minute_trades,
    round(lo, 2) AS month_low_extended,
    formatDateTime(toTimeZone(argMin(window_start, toFloat64(low)), 'America/New_York'), '%Y-%m-%d %H:%i') AS month_low_bar_et,
    countIf(toFloat64(low) <= lo + 0.011) AS bars_within_cent_of_low,
    argMin(transactions, toFloat64(low)) AS low_minute_trades,
    round(minIf(toFloat64(low), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199), 2) AS month_low_regular_hours,
    round(minIf(toFloat64(low), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) - lo, 2) AS rth_minus_extended_low,
    round(toFloat64(sum(volume)) / 1e9, 2) AS month_shares_bn,
    uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS sessions,
    may_bars AS may_2026_bars,
    spy_jun19 AS spy_bars_june19
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-01 00:00:00')