Crypto Exchange Order Book Analytics with Streaming SQL

Crypto exchange order book analytics with streaming SQL means using RisingWave to ingest order book events and trade tape from exchange matching engines, then computing depth metrics, spread statistics, and liquidity scores as continuously updated materialized views—enabling real-time market surveillance, fee optimization, and trader analytics without custom code.

Why Order Book Analytics Is a Streaming Problem

A crypto exchange's order book changes with every new order, cancellation, and trade. At peak market hours, a mid-cap token might see thousands of order book updates per second. Useful analytics—bid-ask spread, depth at various price levels, order flow imbalance, trade-to-cancel ratios—must be computed over recent windows to be actionable.

Batch approaches (hourly or daily summaries) miss the microstructure dynamics that matter most: is liquidity thinning before a large move? Is a market maker withdrawing from one side? Is wash trading occurring? These questions require real-time streaming aggregations, not next-day reports.

RisingWave brings standard SQL syntax—familiar to every data engineer—to this stream processing problem, replacing custom event-driven code with declarative materialized views.

Ingesting Order and Trade Events

-- Order lifecycle events from matching engine
CREATE SOURCE order_events (
    order_id            VARCHAR,
    market_id           VARCHAR,
    trader_id           VARCHAR,
    side                VARCHAR,        -- 'BID' or 'ASK'
    order_type          VARCHAR,        -- 'LIMIT', 'MARKET', 'STOP'
    event_type          VARCHAR,        -- 'PLACED', 'PARTIALLY_FILLED', 'FILLED', 'CANCELLED'
    price               DECIMAL(18,8),
    original_quantity   DECIMAL(18,8),
    remaining_quantity  DECIMAL(18,8),
    event_time          TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'order-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Executed trades from matching engine
CREATE SOURCE trade_executions (
    trade_id            VARCHAR,
    market_id           VARCHAR,
    maker_order_id      VARCHAR,
    taker_order_id      VARCHAR,
    maker_trader_id     VARCHAR,
    taker_trader_id     VARCHAR,
    price               DECIMAL(18,8),
    quantity            DECIMAL(18,8),
    taker_side          VARCHAR,        -- 'BUY' or 'SELL'
    trade_time          TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'trade-executions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Order Book Depth Analytics

-- Aggregate open orders by price level (current book depth snapshot)
CREATE MATERIALIZED VIEW order_book_depth AS
SELECT
    market_id,
    side,
    price,
    SUM(remaining_quantity)             AS total_quantity,
    COUNT(*)                            AS order_count,
    MIN(event_time)                     AS oldest_order_time
FROM order_events
WHERE event_type IN ('PLACED', 'PARTIALLY_FILLED')
  AND remaining_quantity > 0
GROUP BY market_id, side, price;

-- Top-of-book and spread metrics
CREATE MATERIALIZED VIEW market_spread_metrics AS
SELECT
    market_id,
    MAX(price) FILTER (WHERE side = 'BID')                          AS best_bid,
    MIN(price) FILTER (WHERE side = 'ASK')                          AS best_ask,
    MIN(price) FILTER (WHERE side = 'ASK')
        - MAX(price) FILTER (WHERE side = 'BID')                    AS spread,
    CASE WHEN MAX(price) FILTER (WHERE side = 'BID') > 0
         THEN (MIN(price) FILTER (WHERE side = 'ASK')
               - MAX(price) FILTER (WHERE side = 'BID'))
              / MAX(price) FILTER (WHERE side = 'BID')
         ELSE NULL END                                               AS spread_bps,
    SUM(total_quantity) FILTER (WHERE side = 'BID')                 AS total_bid_depth,
    SUM(total_quantity) FILTER (WHERE side = 'ASK')                 AS total_ask_depth
FROM order_book_depth
GROUP BY market_id;

-- 1-minute OHLCV candles
CREATE MATERIALIZED VIEW ohlcv_1min AS
SELECT
    window_start,
    window_end,
    market_id,
    MIN(price)                                          AS low,
    MAX(price)                                          AS high,
    SUM(quantity)                                       AS volume,
    SUM(price * quantity) / NULLIF(SUM(quantity), 0)   AS vwap,
    COUNT(*)                                            AS trade_count,
    SUM(quantity) FILTER (WHERE taker_side = 'BUY')    AS buy_volume,
    SUM(quantity) FILTER (WHERE taker_side = 'SELL')   AS sell_volume
FROM TUMBLE(trade_executions, trade_time, INTERVAL '1 MINUTE')
GROUP BY window_start, window_end, market_id;

Market Surveillance: Wash Trading Detection

Wash trading—where a trader buys and sells to themselves to create artificial volume—is a serious compliance concern for crypto exchanges. Streaming SQL can flag potential wash trades in real time:

-- Flag potential wash trades: same trader on both sides within a short window
CREATE MATERIALIZED VIEW wash_trade_candidates AS
SELECT
    window_start,
    window_end,
    market_id,
    maker_trader_id,
    COUNT(*) FILTER (WHERE maker_trader_id = taker_trader_id)   AS self_trades,
    COUNT(*)                                                     AS total_trades,
    SUM(quantity) FILTER (WHERE maker_trader_id = taker_trader_id) AS self_trade_volume,
    SUM(quantity)                                                AS total_volume
FROM TUMBLE(trade_executions, trade_time, INTERVAL '5 MINUTES')
GROUP BY window_start, window_end, market_id, maker_trader_id
HAVING COUNT(*) FILTER (WHERE maker_trader_id = taker_trader_id) > 0;

-- Trader order cancel ratio: high cancel rates indicate potential spoofing
CREATE MATERIALIZED VIEW trader_cancel_ratio AS
SELECT
    window_start,
    window_end,
    market_id,
    trader_id,
    COUNT(*) FILTER (WHERE event_type = 'PLACED')       AS orders_placed,
    COUNT(*) FILTER (WHERE event_type = 'CANCELLED')    AS orders_cancelled,
    COUNT(*) FILTER (WHERE event_type = 'FILLED')       AS orders_filled,
    CASE WHEN COUNT(*) FILTER (WHERE event_type = 'PLACED') > 0
         THEN COUNT(*) FILTER (WHERE event_type = 'CANCELLED')::DECIMAL
              / COUNT(*) FILTER (WHERE event_type = 'PLACED')
         ELSE 0 END                                     AS cancel_ratio
FROM TUMBLE(order_events, event_time, INTERVAL '5 MINUTES')
GROUP BY window_start, window_end, market_id, trader_id
HAVING COUNT(*) FILTER (WHERE event_type = 'PLACED') > 10;

Liquidity Provider Performance

-- Market maker performance: uptime and spread contribution
CREATE MATERIALIZED VIEW market_maker_performance AS
SELECT
    window_start,
    window_end,
    market_id,
    maker_trader_id,
    COUNT(*)                                AS trades_as_maker,
    SUM(quantity)                           AS maker_volume,
    AVG(price)                              AS avg_maker_price,
    SUM(quantity * price)                   AS maker_notional
FROM TUMBLE(trade_executions, trade_time, INTERVAL '1 HOUR')
GROUP BY window_start, window_end, market_id, maker_trader_id;

Comparison: Custom Event Processing vs. Streaming SQL for Exchange Analytics

Dimension	Custom Event Processing Code	Streaming SQL with RisingWave
Development time	Weeks per analytics feature	Hours
OHLCV candle computation	Custom aggregation logic	Single TUMBLE() query
Multi-window support	Separate code per window	Multiple TUMBLE/HOP views
Wash trade detection	Complex state machine	SQL HAVING clause
Dashboard integration	Custom API layer needed	PostgreSQL wire protocol
New metric iteration	Redeploy required	ALTER view or new view

FAQ

Q: Can RisingWave serve real-time order book data to trading frontends? A: Yes. Query market_spread_metrics and order_book_depth via the PostgreSQL wire protocol. For websocket-based frontends, create a Kafka sink from these views and have the frontend consume via a WebSocket gateway that reads from Kafka. This gives sub-second push updates without long-polling.

Q: How does RisingWave handle the state explosion from tracking every price level in the order book? A: The order_book_depth view groups by (market_id, side, price). For active markets with many price levels, this view can have many rows, but RisingWave handles this efficiently as it only updates the rows affected by each new order event. For memory efficiency, prune stale price levels (where total_quantity = 0) using a WHERE clause in downstream views.

Q: Can we compute implied volatility from trade prices using RisingWave? A: IV computation requires options pricing models (Black-Scholes) that are not native SQL. The practical approach is to compute realized volatility (standard deviation of log returns) natively in SQL, then feed prices to an external IV calculation service that writes results back to a Kafka topic RisingWave can ingest.

Q: How do we handle multiple trading pairs (BTC/USD, ETH/USD, etc.) in the same pipeline? A: The market_id column handles multi-pair routing. All pairs flow through the same Kafka topics and get partitioned by market_id in materialized views. No schema changes are needed to add a new trading pair—just start sending events with the new market_id value.

Q: Can we sink order book analytics to a time-series database like TimescaleDB? A: Yes. Use CREATE SINK ... WITH (connector = 'jdbc') to write materialized view results to TimescaleDB (which is PostgreSQL-compatible). This is useful for historical charting where you want TimescaleDB's hypertable compression for long-term OHLCV storage while RisingWave serves real-time queries.

Get Started

Build your crypto exchange analytics pipeline with the RisingWave documentation.
Join the RisingWave Slack to discuss exchange and trading platform use cases.