curve_dispersion

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
        et_date,
        formatDateTime(toStartOfInterval(et, INTERVAL 30 MINUTE), '%H:%i', 'America/New_York') AS et_time,
        sum(shares)                                                                            AS bucket_shares
    FROM bars
    WHERE minute_of_day >= 570
      AND minute_of_day <  960
    GROUP BY et_date, et_time
),
per_day AS
(
    SELECT
        et_date,
        sum(bucket_shares) AS day_shares
    FROM per_bucket
    GROUP BY et_date
)
SELECT
    b.et_time                                                                                          AS et_time,
    round(100 * quantileDeterministic(0.5)(b.bucket_shares / d.day_shares, toYYYYMMDD(b.et_date)), 2)  AS median_share_pct,
    round(100 * min(b.bucket_shares / d.day_shares), 2)                                                AS lowest_share_pct,
    round(100 * max(b.bucket_shares / d.day_shares), 2)                                                AS highest_share_pct
FROM per_bucket AS b
INNER JOIN per_day AS d ON d.et_date = b.et_date
GROUP BY et_time
ORDER BY et_time