Persistence check: the eight leaders' daily relative volume across the five sessions

WITH sess AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(volume)) AS vol,
           sum(toFloat64(close) * toFloat64(volume)) AS dollars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 70 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, d
),
cal AS (
    SELECT d, row_number() OVER (ORDER BY d DESC) AS rn
    FROM (SELECT DISTINCT d FROM sess)
),
per_name AS (
    SELECT s.ticker AS ticker,
           avgIf(s.vol, c.rn <= 5) AS adv_recent,
           avgIf(s.vol, c.rn BETWEEN 6 AND 45) AS adv_base,
           sumIf(s.dollars, c.rn <= 5) AS dollar_recent,
           countIf(c.rn <= 5) AS recent_sessions,
           countIf(c.rn BETWEEN 6 AND 45) AS base_sessions
    FROM sess s INNER JOIN cal c ON s.d = c.d
    GROUP BY s.ticker
    HAVING adv_base > 100000 AND dollar_recent >= 500000000 AND recent_sessions = 5 AND base_sessions >= 35
),
leaders AS (
    SELECT ticker, adv_recent / adv_base AS rvol_week, adv_base
    FROM per_name
    ORDER BY rvol_week DESC, ticker ASC
    LIMIT 8
),
daily AS (
    SELECT l.ticker AS ticker,
           l.rvol_week AS rvol_week,
           c.rn AS rn,
           s.vol / l.adv_base AS rvol_day
    FROM sess s
    INNER JOIN cal c ON s.d = c.d
    INNER JOIN leaders l ON s.ticker = l.ticker
    WHERE c.rn <= 5
)
SELECT ticker,
       round(max(rvol_week), 1) AS week_rvol,
       countIf(rvol_day >= 2) AS days_above_2x,
       round(max(rvol_day), 1) AS peak_day_rvol,
       argMax(rn, (rvol_day, -rn)) - 1 AS sessions_since_peak
FROM daily
GROUP BY ticker
ORDER BY days_above_2x ASC, peak_day_rvol ASC, ticker ASC