Liquidity comparison across instruments

Compare liquidity across instruments by calculating the effective spread at a given order size. The effective spread measures the actual cost of executing a round-trip trade (buy then sell) using Level 2 order book data.

The problem​

You have order book snapshots for multiple instruments and want to compare which ones offer better liquidity for a target order size. The quoted spread (best bid vs best ask) does not tell the full story. Larger orders eat through multiple price levels.

Solution: Use L2Price to calculate effective spread​

L2Price calculates the average execution price when filling an order against multiple price levels. The effective spread is the difference between the buy and sell execution prices for a given size.

Compare effective spread across instrumentsDemo this query
WITH latest_books AS (
  SELECT timestamp, symbol, bids, asks
  FROM market_data
  WHERE timestamp IN today()
  LATEST ON timestamp PARTITION BY symbol
)
SELECT
  symbol,
  L2PRICE(100_000, asks[2], asks[1]) AS buy_price,
  L2PRICE(100_000, bids[2], bids[1]) AS sell_price,
  L2PRICE(100_000, asks[2], asks[1]) - L2PRICE(100_000, bids[2], bids[1]) AS effective_spread,
  (L2PRICE(100_000, asks[2], asks[1]) - L2PRICE(100_000, bids[2], bids[1])) /
    ((L2PRICE(100_000, asks[2], asks[1]) + L2PRICE(100_000, bids[2], bids[1])) / 2) * 10_000 AS spread_bps
FROM latest_books
ORDER BY spread_bps;

This query:

  1. Gets the latest order book snapshot for each symbol using LATEST ON
  2. Calculates the average execution price for buying and selling 100,000 units
  3. Computes the effective spread in absolute terms and basis points
  4. Ranks instruments by liquidity (lowest spread = most liquid)

Effective spread over time​

Track how liquidity changes throughout the trading day:

Effective spread time-seriesDemo this query
SELECT
  timestamp,
  symbol,
  last((L2PRICE(100_000, asks[2], asks[1]) - L2PRICE(100_000, bids[2], bids[1])) /
    ((L2PRICE(100_000, asks[2], asks[1]) + L2PRICE(100_000, bids[2], bids[1])) / 2)) * 10_000 AS spread_bps
FROM market_data
WHERE timestamp IN today()
  AND symbol IN ('EURUSD', 'GBPUSD', 'USDJPY')
SAMPLE BY 1h
ORDER BY timestamp, symbol;

Compare liquidity at different order sizes​

See how execution costs scale with order size:

Liquidity depth analysisDemo this query
WITH latest_books AS (
  SELECT symbol, bids, asks
  FROM market_data
  WHERE timestamp IN today()
  LATEST ON timestamp PARTITION BY symbol
)
SELECT
  symbol,
  L2PRICE(10_000, asks[2], asks[1]) - L2PRICE(10_000, bids[2], bids[1]) AS spread_10k,
  L2PRICE(100_000, asks[2], asks[1]) - L2PRICE(100_000, bids[2], bids[1]) AS spread_100k,
  L2PRICE(500_000, asks[2], asks[1]) - L2PRICE(500_000, bids[2], bids[1]) AS spread_500k,
  L2PRICE(1_000_000, asks[2], asks[1]) - L2PRICE(1_000_000, bids[2], bids[1]) AS spread_1m
FROM latest_books
ORDER BY symbol;

Instruments with similar spreads across sizes have deeper liquidity.

Interpreting results​

  • Lower spread_bps = better liquidity, lower trading costs
  • Spread widening with size = shallow order book, higher market impact
  • NULL values = insufficient liquidity to fill the target size
Related documentation