3,256 answered market questions

WITH
    quotes AS
    (
        SELECT
            ticker,
            sip_timestamp,
            toFloat64(bid_price + ask_price) / 2 AS mid,
            toFloat64(ask_price - bid_price) / 2 AS half_spread
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= toDateTime('2026-09-16 14:00:00', 'UTC')
          AND sip_timestamp <  toDateTime('2026-09-16 15:00:00', 'UTC')
          AND bid_price > 0
          AND ask_price > bid_price
    ),
    fills AS
    (
        SELECT
            ticker,
            sip_timestamp,
            toFloat64(price) AS fill_price,
            size
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= toDateTime('2026-09-16 14:00:00', 'UTC')
          AND sip_timestamp <  toDateTime('2026-09-16 15:00:00', 'UTC')
          AND price > 0
          AND size > 0
    )
SELECT
    multiIf(f.size < 100,  '1 to 99 shares',
            f.size < 500,  '100 to 499',
            f.size < 1000, '500 to 999',
            f.size < 5000, '1,000 to 4,999',
                           '5,000 or more')                                      AS size_bucket,
    round(avg(abs(f.fill_price - q.mid) / q.mid) * 10000, 3)                      AS avg_distance_bps,
    round(100 * countIf(abs(f.fill_price - q.mid) > q.half_spread) / count(), 2)  AS pct_outside_touch
FROM fills AS f
ASOF JOIN quotes AS q ON f.ticker = q.ticker AND f.sip_timestamp >= q.sip_timestamp
GROUP BY size_bucket
ORDER BY min(f.size)