Effective spread against quoted spread by trade size, AAPL

SELECT
    size_bucket,
    round(avg(quoted_bps), 2)                            AS avg_quoted_spread_bps,
    round(avg(effective_bps), 2)                         AS avg_effective_spread_bps,
    round(100 * avg(effective_bps) / avg(quoted_bps), 1) AS eq_over_q_pct
FROM
(
    SELECT
        multiIf(t.size < 100,  '1 to 99 (odd lot)',
                t.size < 500,  '100 to 499',
                t.size < 2000, '500 to 1,999',
                               '2,000 and up')                                AS size_bucket,
        multiIf(t.size < 100, 1, t.size < 500, 2, t.size < 2000, 3, 4)         AS bucket_order,
        10000 * (q.ask - q.bid) / ((q.ask + q.bid) / 2)                        AS quoted_bps,
        10000 * 2 * abs(t.price - ((q.ask + q.bid) / 2)) / ((q.ask + q.bid) / 2) AS effective_bps
    FROM
    (
        SELECT ticker, sip_timestamp, toFloat64(price) AS price, size
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-06-17 14:00:00'
          AND sip_timestamp <  '2026-06-17 15:00:00'
          AND size > 0
          AND price > 0
    ) AS t
    ASOF JOIN
    (
        SELECT ticker, sip_timestamp, toFloat64(bid_price) AS bid, toFloat64(ask_price) AS ask
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-06-17 13:45:00'
          AND sip_timestamp <  '2026-06-17 15:00:00'
          AND bid_price > 0
          AND ask_price > bid_price
    ) AS q
    ON t.ticker = q.ticker AND t.sip_timestamp >= q.sip_timestamp
)
WHERE effective_bps < 200 AND quoted_bps < 200
GROUP BY size_bucket, bucket_order
ORDER BY bucket_order