Liquidity comparison across instruments - Questdb

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.

questdb-sql

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:

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

Effective spread over time

Track how liquidity changes throughout the trading day:

questdb-sql

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:

questdb-sql

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

:::info Related documentation