3,256 answered market questions

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.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(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(day1_turnover <   5000000, 'under $5M traded',
            day1_turnover <  50000000, '$5M to $50M',
            day1_turnover < 500000000, '$50M to $500M',
                                       '$500M and up') AS day1_turnover_tier,
    count()                                            AS listings,
    round(quantileDeterministic(0.5)(offer_to_open, cityHash64(ticker)), 1) AS median_offer_to_open_pct,
    round(quantileDeterministic(0.9)(offer_to_open, cityHash64(ticker))
        - quantileDeterministic(0.1)(offer_to_open, cityHash64(ticker)), 1) AS p10_to_p90_spread
FROM day_one
GROUP BY day1_turnover_tier
ORDER BY min(day1_turnover)