Full-day RVOL percentiles across high-volume US stocks and ETFs (20-day ADV above 5M shares), latest completed session

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           toFloat64(sum(volume)) AS day_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 40 DAY
      AND toDate(toTimeZone(window_start, 'America/New_York')) < toDate(toTimeZone(now(), 'America/New_York'))
    GROUP BY ticker, et_date
),
spy_days AS (
    SELECT et_date,
           day_volume,
           avg(day_volume) OVER (ORDER BY et_date ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS prior_avg
    FROM daily
    WHERE ticker = 'SPY' AND day_volume > 10000000
),
sessions AS (
    SELECT et_date FROM spy_days
),
latest AS (
    SELECT max(et_date) AS d FROM spy_days WHERE prior_avg > 0 AND day_volume >= 0.6 * prior_avg
),
prior20 AS (
    SELECT et_date FROM sessions WHERE et_date < (SELECT d FROM latest) ORDER BY et_date DESC LIMIT 20
),
rvols AS (
    SELECT ticker, day_vol / adv20 AS rvol
    FROM (
        SELECT ticker,
               sumIf(day_volume, et_date = (SELECT d FROM latest)) AS day_vol,
               sumIf(day_volume, et_date IN (SELECT et_date FROM prior20)) / 20 AS adv20,
               countIf(day_volume > 0 AND et_date IN (SELECT et_date FROM prior20)) AS sessions_traded
        FROM daily
        WHERE ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING adv20 > 5000000 AND day_vol > 0 AND sessions_traded = 20
    )
)
SELECT pair.1 AS percentile,
       round(pair.2, 2) AS rvol
FROM (
    SELECT arrayJoin(arrayZip(['p10', 'p25', 'p50 (median)', 'p75', 'p90', 'p99'], quantilesExact(0.1, 0.25, 0.5, 0.75, 0.9, 0.99)(rvol))) AS pair
    FROM rvols
)
ORDER BY percentile