Medline (MDLN): the 20 sessions before its 180-day mark vs. every session from it, regular-hours volume and daily range

WITH ipo AS (
    SELECT concat(formatDateTime(listing_date, '%b'), ' ', toString(toDayOfMonth(listing_date)), ', ', toString(toYear(listing_date))) AS listed_on,
           round(max_shares_offered / 1e6, 1) AS shares_offered_m
    FROM global_markets.stocks_ipos
    WHERE ticker = 'MDLN'
      AND ipo_status = 'history'
      AND listing_date >= '2025-12-01'
      AND listing_date <= '2025-12-31'
    LIMIT 1
),
daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           toFloat64(max(high)) AS hi,
           toFloat64(min(low)) AS lo,
           sum(toFloat64(volume)) AS shares_traded
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MDLN'
      AND window_start >= toDateTime('2026-05-15 09:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
      AND toHour(toTimeZone(window_start, 'America/New_York')) >= 9
      AND toHour(toTimeZone(window_start, 'America/New_York')) < 16
      AND NOT (toHour(toTimeZone(window_start, 'America/New_York')) = 9
               AND toMinute(toTimeZone(window_start, 'America/New_York')) < 30)
    GROUP BY session
    HAVING lo > 0
)
SELECT if(session < toDate('2026-06-15'), '1. 20 sessions before', '2. from the 180-day mark') AS window,
       any(listed_on) AS listed_on,
       any(shares_offered_m) AS shares_offered_m,
       count() AS sessions,
       concat(formatDateTime(min(session), '%b'), ' ', toString(toDayOfMonth(min(session)))) AS first_session,
       concat(formatDateTime(max(session), '%b'), ' ', toString(toDayOfMonth(max(session)))) AS last_session,
       round(quantileDeterministic(0.5)(shares_traded, cityHash64(session)) / 1e6, 2) AS median_daily_volume_m,
       round(quantileDeterministic(0.5)(100 * (hi - lo) / lo, cityHash64(session)), 2) AS median_daily_range_pct
FROM daily
CROSS JOIN ipo
GROUP BY window
ORDER BY window