Days from announced deal to first trade, US listings since January 2025

WITH deals AS
(
    SELECT
        ticker,
        max(listing_date)   AS listed_on,
        min(announced_date) AS announced_on
    FROM global_markets.stocks_ipos
    WHERE listing_date >= '2025-01-01'
      AND listing_date <  '2026-06-01'
      AND final_issue_price > 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING announced_on > toDate('2015-01-01')
       AND announced_on < listed_on
)
SELECT
    multiIf(b = 0, 'under 30 days',
            b = 6, '180 days or more',
            concat(toString(b * 30), ' to ', toString(b * 30 + 29), ' days')) AS bucket,
    deals_in_bucket,
    round(100 * deals_in_bucket / sum(deals_in_bucket) OVER (), 1)            AS share_pct
FROM
(
    SELECT
        least(intDiv(dateDiff('day', announced_on, listed_on), 30), 6) AS b,
        count()                                                        AS deals_in_bucket
    FROM deals
    GROUP BY b
)
ORDER BY b