3,256 answered market questions

WITH bars AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York'))           AS et_date,
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
            + toMinute(toTimeZone(window_start, 'America/New_York'))   AS minute_of_day,
        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')
    UNION ALL
    SELECT
        toDate(toTimeZone(sip_timestamp, 'America/New_York'))          AS et_date,
        959                                                            AS minute_of_day,
        toFloat64(maxIf(size, has(conditions, 8)))                     AS shares
    FROM global_markets.stocks_trades
    WHERE ticker = 'SPY'
      AND sip_timestamp >= toDateTime('2026-06-01 00:00:00', 'UTC')
      AND sip_timestamp <  toDateTime('2026-09-01 00:00:00', 'UTC')
      AND toHour(sip_timestamp, 'America/New_York') IN (13, 16)
      AND toMinute(sip_timestamp, 'America/New_York') < 10
    GROUP BY et_date
    HAVING countIf(has(conditions, 8)) > 0
),
per_bucket AS
(
    SELECT
        formatDateTime(toDateTime(intDiv(minute_of_day, 30) * 30 * 60, 'UTC'), '%H:%i') 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