2,170 answered market questions

WITH quotes AS
(
    SELECT
        ticker,
        toUInt64(sequence_number)           AS weight,
        toFloat64(bid_price)                AS bid,
        toFloat64(ask_price)                AS ask,
        toFloat64(bid_size + ask_size) / 2  AS touch_lots
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('SPY', 'AAPL', 'MSFT', 'PG', 'KO')
      AND sip_timestamp >= '2026-06-16 14:30:00'
      AND sip_timestamp <  '2026-06-16 15:30:00'
      AND bid_price > 0
      AND ask_price > bid_price
)
SELECT
    ticker                                                                                       AS symbol,
    round(quantileDeterministic(0.5)(10000 * (ask - bid) / ((ask + bid) / 2), weight), 2)        AS quoted_spread_bps,
    round(quantileDeterministic(0.5)(touch_lots, weight), 1)                                     AS touch_size_lots
FROM quotes
GROUP BY ticker
ORDER BY quoted_spread_bps