H1 2026 listings vs their offer price: last regular-hours close of July 6-10, by type

SELECT multiIf(i.security_type IN ('SP', 'UNIT'), 'Unit offerings',
               i.security_type = 'ADRC', 'ADRs',
               'Common or ordinary shares') AS listing_type,
       count() AS deals_with_offer_price,
       countIf(p.last_close > 0) AS traded_jul6_10,
       countIf(p.last_close > toFloat64(i.final_issue_price)) AS above_offer,
       countIf(p.last_close > 0 AND p.last_close < toFloat64(i.final_issue_price)) AS below_offer,
       countIf(p.last_close = toFloat64(i.final_issue_price)) AS at_offer,
       round(quantileExactIf(0.5)((p.last_close / toFloat64(i.final_issue_price) - 1) * 100, p.last_close > 0), 1) AS median_return_pct
FROM global_markets.stocks_ipos AS i
LEFT JOIN (
    SELECT ticker, toFloat64(argMax(close, window_start)) AS last_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= toDateTime('2026-07-06 00:00:00')
      AND window_start < toDateTime('2026-07-11 00:00:00')
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    GROUP BY ticker
) AS p ON p.ticker = i.ticker
WHERE i.ipo_status = 'history'
  AND i.currency_code = 'USD'
  AND i.listing_date >= '2026-01-01'
  AND i.listing_date <= '2026-06-30'
  AND i.final_issue_price > 0
GROUP BY listing_type
HAVING countIf(p.last_close > 0) > 0
ORDER BY deals_with_offer_price DESC