3,256 answered market questions

WITH session_minutes AS
(
    SELECT
        ticker,
        formatDateTime(
            toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE),
            '%H:%i')                                                   AS et_time,
        toFloat64(volume)                                              AS share_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'AAPL')
      AND window_start >= '2025-01-02 00:00:00'
      AND window_start <  '2026-07-01 00:00:00'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
),
by_bucket AS
(
    SELECT
        ticker,
        et_time,
        sum(share_volume) AS bucket_volume
    FROM session_minutes
    GROUP BY ticker, et_time
),
ticker_totals AS
(
    SELECT
        ticker,
        sum(bucket_volume) AS ticker_volume
    FROM by_bucket
    GROUP BY ticker
)
SELECT
    b.et_time                                                          AS et_time,
    round(100 * sumIf(b.bucket_volume, b.ticker = 'SPY')
          / max(if(t.ticker = 'SPY', t.ticker_volume, 0)), 2)          AS spy_pct,
    round(100 * sumIf(b.bucket_volume, b.ticker = 'AAPL')
          / max(if(t.ticker = 'AAPL', t.ticker_volume, 0)), 2)         AS aapl_pct
FROM by_bucket AS b
INNER JOIN ticker_totals AS t ON t.ticker = b.ticker
GROUP BY b.et_time
ORDER BY b.et_time