volume_curve

WITH bars AS
(
    SELECT
        toTimeZone(window_start, 'America/New_York')  AS et,
        toHour(et) * 60 + toMinute(et)                AS minute_of_day,
        toDate(et)                                    AS et_date,
        toFloat64(volume)                             AS shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-06-01 00:00:00', 'UTC')
      AND window_start <  toDateTime('2026-09-01 00:00:00', 'UTC')
),
per_bucket AS
(
    SELECT
        formatDateTime(toStartOfInterval(et, INTERVAL 30 MINUTE), '%H:%i', 'America/New_York') AS et_time,
        sum(shares)                                                                            AS bucket_shares,
        countDistinct(et_date)                                                                 AS sessions
    FROM bars
    WHERE minute_of_day >= 570
      AND minute_of_day <  960
    GROUP BY et_time
)
SELECT
    b.et_time                                          AS et_time,
    round(b.bucket_shares / b.sessions / 1e6, 2)       AS avg_volume_millions,
    round(100 * b.bucket_shares / t.window_shares, 2)  AS vwap_share_pct,
    round(100 / t.bucket_count, 2)                     AS twap_share_pct
FROM per_bucket AS b
CROSS JOIN
(
    SELECT
        sum(bucket_shares) AS window_shares,
        count()            AS bucket_count
    FROM per_bucket
) AS t
ORDER BY et_time