US listings clearing 1.5x the offer price on the first close, by listing year

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 offers AS
(
    SELECT
        ticker,
        min(listing_date)                 AS listed_on,
        toFloat64(max(final_issue_price)) AS offer_price
    FROM global_markets.stocks_ipos
    WHERE final_issue_price > 0
      AND listing_date >= '2019-01-01'
      AND listing_date <  '2026-07-01'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
),
debut AS
(
    SELECT
        o.ticker                           AS ticker,
        toYear(o.listed_on)                AS listing_year,
        o.offer_price                      AS offer_price,
        argMin(toFloat64(d.close), d.date) AS first_close
    FROM global_markets.stocks_daily_aggs AS d
    INNER JOIN offers AS o ON d.ticker = o.ticker
    WHERE d.date >= '2019-01-01'
      AND d.date >= o.listed_on
      AND d.date <  o.listed_on + 10
    GROUP BY ticker, listing_year, offer_price
)
SELECT
    listing_year AS year,
    count()      AS listing_count,
    round(100 * countIf(first_close >= 1.5 * offer_price) / count(), 1)                    AS cleared_1_5x_day_one_pct,
    round(quantileDeterministic(0.5)(first_close / offer_price, cityHash64(ticker)), 2)    AS median_first_close_ratio
FROM debut
GROUP BY year
ORDER BY year