Share of US listings trading at 1.5x their offer price, by day since listing

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 >= '2021-01-01'
      AND listing_date <  '2026-01-01'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
),
first_touch AS
(
    SELECT
        o.ticker                                          AS ticker,
        countIf(toFloat64(d.high) >= 1.5 * o.offer_price) AS touch_sessions,
        minIf(dateDiff('day', o.listed_on, d.date),
              toFloat64(d.high) >= 1.5 * o.offer_price)   AS days_to_touch
    FROM global_markets.stocks_daily_aggs AS d
    INNER JOIN offers AS o ON d.ticker = o.ticker
    WHERE d.date >= '2021-01-01'
      AND d.date <  '2026-07-01'
      AND d.date >= o.listed_on
      AND d.date <  o.listed_on + 181
    GROUP BY o.ticker
)
SELECT
    grid.day AS days_since_listing,
    round(100 * countIf(t.touch_sessions > 0 AND t.days_to_touch <= grid.day) / count(), 1) AS reached_1_5x_pct
FROM first_touch AS t
CROSS JOIN
(
    SELECT arrayJoin(range(0, 190, 10)) AS day
) AS grid
GROUP BY days_since_listing
ORDER BY days_since_listing