One AAPL session, four lookbacks: full-day relative volume from daily bars

WITH toDate('2026-09-10') AS session_day
SELECT
    lookback,
    round(session_volume / 1e6, 1)            AS session_volume_millions,
    round(average_volume / 1e6, 1)            AS average_volume_millions,
    round(session_volume / average_volume, 2) AS rvol_ratio
FROM
(
    SELECT
        date,
        vol                                                                     AS session_volume,
        avg(vol) OVER (ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) AS avg10,
        avg(vol) OVER (ORDER BY date ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS avg20,
        avg(vol) OVER (ORDER BY date ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING) AS avg50,
        avg(vol) OVER (ORDER BY date ROWS BETWEEN 63 PRECEDING AND 1 PRECEDING) AS avg63,
        count()  OVER (ORDER BY date ROWS BETWEEN 63 PRECEDING AND 1 PRECEDING) AS prior_sessions
    FROM
    (
        SELECT date, max(toFloat64(volume)) AS vol
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= session_day - 110
          AND date <= session_day
        GROUP BY date
    )
)
ARRAY JOIN
    ['10 sessions', '20 sessions', '50 sessions', '63 sessions (about 3 months)'] AS lookback,
    [10, 20, 50, 63]                                                                 AS n,
    [avg10, avg20, avg50, avg63]                                                     AS average_volume
WHERE date = session_day
  AND prior_sessions >= 63
  AND session_volume > 0
ORDER BY n