opening_move_vs_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 day_one AS
(
    SELECT
        i.ticker                                                 AS ticker,
        i.list_date                                              AS list_date,
        (toFloat64(d.open_px)  / i.offer_price - 1) * 100        AS offer_to_open,
        (toFloat64(d.close_px) / toFloat64(d.open_px) - 1) * 100 AS open_to_close,
        toFloat64(d.open_px) * toFloat64(d.day_volume)           AS day1_turnover
    FROM
    (
        SELECT
            ticker,
            toDate(argMax(listing_date, last_updated))         AS list_date,
            argMax(toFloat64(final_issue_price), last_updated) AS offer_price
        FROM global_markets.stocks_ipos
        WHERE listing_date >= '2017-01-01'
          AND listing_date <  '2026-09-01'
          AND final_issue_price > 0
          AND currency_code = 'USD'
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
    ) AS i
    INNER JOIN
    (
        SELECT
            ticker,
            date,
            max(open)   AS open_px,
            max(close)  AS close_px,
            max(volume) AS day_volume
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2017-01-01'
          AND date <  '2026-09-01'
        GROUP BY ticker, date
    ) AS d
        ON d.ticker = i.ticker AND d.date = i.list_date
    WHERE d.day_volume > 0
      AND d.open_px > 0
      AND i.offer_price > 0
)
SELECT
    multiIf(offer_to_open <  0, 'opened below offer',
            offer_to_open < 10, 'opened 0 to 10% up',
            offer_to_open < 30, 'opened 10 to 30% up',
            offer_to_open < 60, 'opened 30 to 60% up',
                                'opened over 60% up') AS opening_move_bucket,
    count()                                           AS listings,
    round(quantileDeterministic(0.5)(open_to_close, cityHash64(ticker)), 1) AS median_open_to_close_pct
FROM day_one
GROUP BY opening_move_bucket
ORDER BY min(offer_to_open)