3,293 answered market questions

WITH bars AS (
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York'))            AS trade_day,
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
            + toMinute(toTimeZone(window_start, 'America/New_York'))    AS et_minute,
        high,
        low,
        volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'QQQ', 'AAPL', 'MSFT', 'NVDA', 'KO')
      AND window_start >= '2023-01-01'
      AND window_start <  '2026-01-01'
),
regular AS (
    SELECT *
    FROM bars
    WHERE et_minute >= 570 AND et_minute < 960
),
day_totals AS (
    SELECT
        ticker,
        trade_day,
        max(high)   AS day_high,
        min(low)    AS day_low,
        sum(volume) AS day_volume,
        count()     AS bar_count
    FROM regular
    GROUP BY ticker, trade_day
    HAVING bar_count >= 300
       AND day_high > day_low
       AND day_volume > 0
),
half_hours AS (
    SELECT
        ticker,
        trade_day,
        intDiv(et_minute - 570, 30) AS bucket,
        max(high)                   AS bucket_high,
        min(low)                    AS bucket_low,
        sum(volume)                 AS bucket_volume
    FROM regular
    GROUP BY ticker, trade_day, bucket
)
SELECT
    formatDateTime(toDateTime((570 + h.bucket * 30) * 60, 'UTC'), '%H:%i') AS et_time,
    round(avg(100 * toFloat64(h.bucket_volume) / toFloat64(d.day_volume)), 1) AS volume_share_pct,
    round(avg(100 * toFloat64(h.bucket_high - h.bucket_low)
                  / toFloat64(d.day_high - d.day_low)), 1)                   AS range_share_pct
FROM half_hours AS h
INNER JOIN day_totals AS d
    ON d.ticker = h.ticker AND d.trade_day = h.trade_day
GROUP BY h.bucket
ORDER BY h.bucket