2,173 answered market questions

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           sum(toFloat64(volume)) AS day_shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MU'
      AND window_start >= toDateTime('2025-08-01 00:00: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')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY et_date
),
rolled AS (
    SELECT et_date,
           avg(day_shares) OVER (ORDER BY et_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS adv20,
           avg(day_shares) OVER (ORDER BY et_date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW) AS adv90,
           row_number() OVER (ORDER BY et_date) AS rn,
           count() OVER () AS total_rows
    FROM daily
)
SELECT formatDateTime(et_date, '%Y-%m-%d') AS date,
       round(adv20 / 1e6, 1) AS adv_20_session_m,
       round(adv90 / 1e6, 1) AS adv_90_session_m
FROM rolled
WHERE rn > 90 AND (rn % 3 = total_rows % 3)
ORDER BY et_date