Average quoted spread: liquid mega-caps vs a thin small-cap (past week)

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH early AS (
    SELECT date AS d,
           any(toHour(toTimeZone(close, 'America/New_York')) * 60
               + toMinute(toTimeZone(close, 'America/New_York'))) AS close_min
    FROM global_markets.stocks_market_holidays
    WHERE status = 'early-close'
    GROUP BY date
),
closed AS (
    SELECT DISTINCT date AS d
    FROM global_markets.stocks_market_holidays
    WHERE status = 'closed'
)
SELECT
  q.ticker                                                                           AS ticker,
  round(avg(q.ask_price - q.bid_price) * 100, 1)                                     AS avg_spread_cents,
  round(avg(q.ask_price - q.bid_price) / avg((q.ask_price + q.bid_price) / 2) * 100, 3)  AS spread_pct_of_price,
  round(avg(q.ask_price - q.bid_price) * 100, 2)                                     AS round_trip_100_shares_dollars
FROM (
    SELECT ticker, bid_price, ask_price,
           toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
           toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
             + toMinute(toTimeZone(sip_timestamp, 'America/New_York')) AS et_min
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('AAPL', 'KO', 'NATH')
      AND sip_timestamp >= now() - INTERVAL 7 DAY
      AND bid_price > 0 AND ask_price > bid_price
) AS q
LEFT JOIN early AS e ON q.d = e.d
WHERE q.d NOT IN (SELECT d FROM closed)
  AND q.et_min >= 570
  AND q.et_min < if(e.close_min > 0, e.close_min, 960)
GROUP BY q.ticker
ORDER BY q.ticker