2,236 answered market questions

WITH raw AS (
    SELECT toTimeZone(window_start, 'America/New_York') AS et,
           volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2025-08-01 00:00:00')
      AND window_start < toDateTime('2026-08-01 00:00:00')
),
bars AS (
    SELECT toDate(et) AS session_date,
           toHour(et) * 60 + toMinute(et) AS minute_of_day,
           intDiv((toHour(et) * 60 + toMinute(et)) - 570, 15) AS bucket,
           volume AS shares
    FROM raw
    WHERE toDayOfWeek(et) = 5
      AND (toHour(et) * 60 + toMinute(et)) BETWEEN 570 AND 959
),
full_sessions AS (
    SELECT session_date
    FROM bars
    GROUP BY session_date
    HAVING sumIf(shares, minute_of_day >= 930) >= 0.05 * sum(shares)
)
SELECT formatDateTime(toDateTime(toDate('2026-01-02')) + (570 + b.bucket * 15) * 60, '%H:%i') AS et_time,
       round(100 * sum(b.shares) / sum(sum(b.shares)) OVER (), 2) AS share_of_session_pct,
       round(sum(b.shares) / uniqExact(b.session_date) / 1000000, 2) AS avg_volume_millions
FROM bars AS b
INNER JOIN full_sessions AS s ON b.session_date = s.session_date
GROUP BY b.bucket
ORDER BY b.bucket