3,256 answered market questions

SELECT
    concat(if(cal.h < 10, '0', ''), toString(cal.h), ':00')  AS et_hour,
    round(ifNull(t.trade_count, 0) / 1000, 1)                AS trades_k,
    toUInt32(round(ifNull(t.median_trade_shares, 0), 0))     AS median_trade_shares,
    toUInt32(round(ifNull(t.avg_trade_shares, 0), 0))        AS avg_trade_shares
FROM
(
    SELECT toUInt8(4 + arrayJoin(range(16))) AS h
) AS cal
LEFT JOIN
(
    SELECT
        toHour(toTimeZone(sip_timestamp, 'America/New_York')) AS h,
        count()                                                AS trade_count,
        quantileExact(0.5)(toFloat64(size))                    AS median_trade_shares,
        avg(toFloat64(size))                                   AS avg_trade_shares
    FROM global_markets.stocks_trades
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= toDateTime('2026-09-10 08:00:00', 'UTC')
      AND sip_timestamp <  toDateTime('2026-09-11 00:00:00', 'UTC')
    GROUP BY h
) AS t ON t.h = cal.h
ORDER BY cal.h