Top 10 by full-day RVOL: latest completed session (20-day ADV above 5M shares, full history required)

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
)
SELECT ticker,
       formatDateTime((SELECT d FROM latest), '%Y-%m-%d') AS session_date,
       round(day_vol / 1e6, 1) AS session_volume_m,
       round(adv20 / 1e6, 1) AS adv_20d_m,
       round(day_vol / adv20, 1) 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
)
ORDER BY rvol DESC, ticker
LIMIT 10