execution costs — 4 answered market questions

Distance from the prevailing mid by trade size, AAPL, one midday hour

most recentas of 2026-10-08 ranking 5×3read in context →

Distance from the prevailing mid by trade size, AAPL, one midday hour — 5 rows by 3 columns, computed 2026-10-08 from US exchange, SIP and OPRA data.
size_bucket	avg_distance_bps	pct_outside_touch
1 to 99 shares	0.552	14.31
100 to 499	0.401	14.21
500 to 999	0.63	16.91
1,000 to 4,999	0.523	15.38
5,000 or more	4.638	36.36

the exact SQL behind every number

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)

More from this analysisHow Much Slippage to Assume in a Backtest

Quoted spread and the half spread floor, six household names, one midday hour ranking 6×3 → A naive open-to-close yardstick, netted against a ladder of slippage assumptions ranking 6×4 → A five cent concession as a share of premium, near-the-money SPY contracts by expiry ranking 5×3 → Contracts that traded once, and contracts still trading at expiry series 57×5 → See all 3,214 queries →