Real-time crypto market analytics with streaming SQL means order book depth, spreads, OHLCV candles, VWAP, cross-exchange price divergence, DEX swap volume and fee accrual, price impact, protocol TVL, and mark-to-market P&L are all maintained as continuously updated materialized views that refresh with every order, trade, swap, block, and price tick. This guide builds the full stack on RisingWave, a PostgreSQL-compatible streaming database, for centralized exchanges, multi-venue market data, decentralized exchanges, DeFi protocols, and trading desks.
Why Crypto Market Data Is a Streaming Problem
Crypto markets never close. A mid-cap token's order book changes thousands of times per second at peak, prices on Binance, Coinbase, and Kraken diverge by meaningful percentages within seconds, and every Ethereum block moves pool reserves, lending utilization, and therefore APY. Batch summaries, hourly or daily, miss the microstructure that matters: liquidity thinning before a large move, a market maker withdrawing from one side, a fee tier capturing volume from its neighbors, a position drifting toward a loss limit.
Dune-style analytics platforms are right for historical research and wrong for operational dashboards, because queries run on snapshots and results are cached. Proprietary tick platforms and custom low-latency code solve freshness at high cost. Streaming SQL is the third path: standard CREATE MATERIALIZED VIEW over Kafka-connected feeds, with results that update incrementally and are queryable over the PostgreSQL protocol by any dashboard or trading system.
RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3, so view state survives restarts and can be rebuilt by replaying Kafka. NOW() inside a WHERE or FILTER clause acts as a temporal filter, which is how rolling one-hour and 24-hour windows coexist without separate pipelines.
Sources: Matching Engine, Market Feeds, Chain Events, and Trade Blotter
Five streams cover everything in this guide. Exchanges publish order lifecycle events and trade executions from the matching engine. Market data aggregators normalize quotes and trades from many venues. Chain indexers decode AMM swaps, liquidity events, and protocol events into Kafka. Trading systems publish fills and consume price ticks.
-- Centralized exchange: order lifecycle and executions
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;
CREATE SOURCE trade_executions (
trade_id VARCHAR,
market_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;
-- Multi-venue normalized market data
CREATE SOURCE market_events (
exchange VARCHAR,
trading_pair VARCHAR,
price NUMERIC,
volume NUMERIC,
bid NUMERIC,
ask NUMERIC,
event_type VARCHAR, -- 'trade' or 'quote'
event_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'crypto.market.events',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;
-- Decoded AMM swaps and liquidity events
CREATE SOURCE amm_swaps (
tx_hash VARCHAR,
pool_address VARCHAR,
protocol VARCHAR, -- 'uniswap-v3', 'curve', 'balancer-v2'
fee_tier NUMERIC, -- basis points: 1, 5, 30, 100
symbol_in VARCHAR,
symbol_out VARCHAR,
amount_in_usd NUMERIC,
amount_out_usd NUMERIC,
lp_fee_usd NUMERIC,
protocol_fee_usd NUMERIC,
price_impact_pct NUMERIC,
sender VARCHAR,
block_number BIGINT,
block_ts TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'dex.amm.swaps',
properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;
CREATE SOURCE protocol_events (
event_type VARCHAR, -- 'Deposit', 'Withdraw', 'Borrow', 'Repay', 'Swap', 'Harvest'
protocol_id VARCHAR, -- 'aave-v3', 'compound-v3', 'uniswap-v3', 'curve-3pool'
pool_address VARCHAR,
user_address VARCHAR,
token_symbol VARCHAR,
amount_usd NUMERIC,
fee_usd NUMERIC,
block_ts TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'defi.protocol.events',
properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;
Exchange Order Book Analytics: Depth, Spread, Candles, and Makers
The order book is the sum of open orders by price level. Filtering to orders with remaining quantity and grouping by market, side, and price gives a live depth snapshot, and a second view on top of it gives top of book and spread:
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;
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,
(MIN(price) FILTER (WHERE side = 'ASK') - MAX(price) FILTER (WHERE side = 'BID'))
/ NULLIF(MAX(price) FILTER (WHERE side = 'BID'), 0) * 10000 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;
In production the order stream must be keyed so that a FILLED or CANCELLED event retracts the earlier PLACED row; an upsert source keyed by order_id, or a view that keeps the latest event per order with DISTINCT ON (order_id), gives the depth view the correct current state.
One-minute candles with VWAP and taker-side volume come from the trade tape, and maker performance tells the liquidity team which market makers are earning their rebates:
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;
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,
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;
Open and close prices for a candle use FIRST_VALUE and LAST_VALUE ordered by trade time within the window, which RisingWave supports as window functions over the tumbled stream. The surveillance side of the same data, self-trades and cancel ratios, is covered in the companion guide on crypto risk and market surveillance.
Multi-Venue Price Monitoring: Spreads, Divergence, and Consolidated Best Price
Across venues, the questions are different: where is the tightest spread for BTC/USDT right now, which venue is diverging, and what is the consolidated best bid and offer. A one-minute hopping window sliding every five seconds gives a live spread tracker, and a 24-hour baseline turns it into an anomaly alert:
CREATE MATERIALIZED VIEW live_spread AS
SELECT exchange, trading_pair, window_start, window_end,
AVG(ask - bid) AS avg_spread,
AVG((ask - bid) / NULLIF(bid, 0)) * 100 AS avg_spread_pct,
MAX(ask - bid) AS max_spread,
COUNT(*) AS quote_count,
MAX(event_time) AS last_update
FROM HOP(market_events, event_time, INTERVAL '5 SECONDS', INTERVAL '1 MINUTE')
WHERE event_type = 'quote'
GROUP BY exchange, trading_pair, window_start, window_end;
CREATE MATERIALIZED VIEW spread_baseline_24h AS
SELECT exchange, trading_pair, AVG((ask - bid) / NULLIF(bid, 0)) * 100 AS avg_spread_pct
FROM market_events
WHERE event_type = 'quote' AND event_time > NOW() - INTERVAL '24 HOURS'
GROUP BY exchange, trading_pair;
CREATE MATERIALIZED VIEW spread_alerts AS
SELECT ls.exchange, ls.trading_pair, ls.avg_spread_pct AS current_spread_pct,
b.avg_spread_pct AS baseline_spread_pct, ls.last_update AS alert_time
FROM live_spread ls
JOIN spread_baseline_24h b USING (exchange, trading_pair)
WHERE ls.window_end > NOW() - INTERVAL '10 SECONDS'
AND ls.avg_spread_pct > b.avg_spread_pct * 3;
CREATE MATERIALIZED VIEW consolidated_best_price AS
SELECT trading_pair,
MAX(bid) AS best_bid,
MIN(ask) AS best_offer,
MIN(ask) - MAX(bid) AS consolidated_spread,
COUNT(DISTINCT exchange) AS contributing_venues,
MAX(event_time) AS last_update
FROM market_events
WHERE event_type = 'quote' AND event_time > NOW() - INTERVAL '10 SECONDS'
GROUP BY trading_pair;
The consolidated view is the crypto equivalent of a national best bid and offer. A negative consolidated spread, where one venue's bid exceeds another's ask, is a cross-exchange arbitrage signal and usually also a sign of a stale feed, so the last_update column belongs on every alert.
A 15-minute rolling VWAP with volatility, sliding every minute, is the benchmark algorithmic execution systems compare against:
CREATE MATERIALIZED VIEW vwap_and_volatility AS
SELECT exchange, trading_pair, window_start, window_end,
SUM(price * volume) / NULLIF(SUM(volume), 0) AS vwap,
SUM(volume) AS total_volume,
STDDEV(price) AS price_stddev,
MAX(price) - MIN(price) AS price_range
FROM HOP(market_events, event_time, INTERVAL '1 MINUTE', INTERVAL '15 MINUTES')
WHERE event_type = 'trade'
GROUP BY exchange, trading_pair, window_start, window_end;
DEX Volume, Fees, Fee Tier Share, and Price Impact
Liquidity providers choosing between Uniswap v3 fee tiers need to know where volume is flowing now, traders routing large swaps need the deepest pool, and treasuries need live fee accrual. Rolling one-hour and 24-hour views per pool, plus a fee tier market share view per pair, answer all three:
CREATE MATERIALIZED VIEW pool_volume_1h AS
SELECT pool_address, protocol, symbol_in, symbol_out, fee_tier,
COUNT(*) AS swap_count,
SUM(amount_in_usd) AS volume_usd,
SUM(lp_fee_usd) AS lp_fees_usd,
SUM(protocol_fee_usd) AS protocol_fees_usd,
AVG(amount_in_usd) AS avg_swap_usd,
COUNT(DISTINCT sender) AS unique_traders
FROM amm_swaps
WHERE block_ts > NOW() - INTERVAL '1 HOUR'
GROUP BY pool_address, protocol, symbol_in, symbol_out, fee_tier;
CREATE MATERIALIZED VIEW pool_volume_24h AS
SELECT pool_address, SUM(amount_in_usd) AS volume_usd_24h, SUM(lp_fee_usd) AS lp_fees_usd_24h, COUNT(*) AS swap_count_24h
FROM amm_swaps
WHERE block_ts > NOW() - INTERVAL '24 HOURS'
GROUP BY pool_address;
CREATE MATERIALIZED VIEW fee_tier_market_share AS
SELECT protocol, symbol_in, symbol_out, fee_tier, volume_usd,
volume_usd / NULLIF(SUM(volume_usd) OVER (PARTITION BY protocol, symbol_in, symbol_out), 0) * 100 AS market_share_pct
FROM pool_volume_1h;
CREATE MATERIALIZED VIEW volume_spike_alerts AS
SELECT v1.pool_address, v1.protocol, v1.symbol_in, v1.symbol_out,
v1.volume_usd AS volume_1h, v24.volume_usd_24h / 24 AS avg_hourly_volume,
v1.volume_usd / NULLIF(v24.volume_usd_24h / 24, 0) AS volume_ratio
FROM pool_volume_1h v1
JOIN pool_volume_24h v24 USING (pool_address)
WHERE v24.volume_usd_24h > 0
AND v1.volume_usd > 2 * v24.volume_usd_24h / 24;
Price impact is deterministic on an AMM but changes continuously as liquidity is added, removed, and consumed. The impact coefficient, impact divided by the square root of trade size, is the core metric: a stable coefficient means the pool is absorbing trades proportionally, a rising one means depth is thinning or a large directional order is being worked in pieces.
CREATE MATERIALIZED VIEW pool_impact_stats AS
SELECT pool_address, protocol, symbol_in, symbol_out,
COUNT(*) AS swap_count_1h,
AVG(amount_in_usd) AS avg_swap_size_usd,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount_in_usd) AS p95_swap_size_usd,
AVG(price_impact_pct) AS avg_price_impact_pct,
MAX(price_impact_pct) AS max_price_impact_pct,
AVG(price_impact_pct / NULLIF(SQRT(amount_in_usd), 0)) AS avg_impact_coefficient,
SUM(amount_in_usd) AS total_volume_usd_1h
FROM amm_swaps
WHERE block_ts > NOW() - INTERVAL '1 HOUR'
AND price_impact_pct IS NOT NULL
AND amount_in_usd > 100
GROUP BY pool_address, protocol, symbol_in, symbol_out;
CREATE MATERIALIZED VIEW price_impact_alerts AS
SELECT s.tx_hash, s.pool_address, s.protocol, s.symbol_in, s.symbol_out, s.amount_in_usd, s.price_impact_pct,
s.price_impact_pct / NULLIF(SQRT(s.amount_in_usd), 0) AS impact_coefficient,
st.avg_impact_coefficient AS pool_avg_coefficient,
CASE WHEN s.amount_in_usd > 1000000 THEN 'WHALE_TRADE'
WHEN s.price_impact_pct > 2.0 THEN 'HIGH_IMPACT'
WHEN s.price_impact_pct > 5 * st.avg_price_impact_pct THEN 'IMPACT_ANOMALY'
ELSE 'NOTABLE' END AS alert_type,
s.block_ts
FROM amm_swaps s
JOIN pool_impact_stats st USING (pool_address)
WHERE s.block_ts > NOW() - INTERVAL '5 MINUTES'
AND (s.amount_in_usd > 500000 OR s.price_impact_pct > 2.0 OR s.price_impact_pct > 5 * st.avg_price_impact_pct);
Comparing pool_impact_stats across protocols for the same pair is a routing input: the pool with the lowest impact coefficient at the p95 swap size is where a large order should go. A single swap with unexpectedly high slippage is a thin market, a sandwich attack, or a bad router quote, and all three deserve a look.
DeFi Protocol Dashboards: TVL, Revenue, and Flow Alerts
A protocol's state changes every block. A large deposit shifts a lending pool's utilization and therefore every borrower's rate. TVL is a running sum of deposits and borrows minus withdrawals and repayments, revenue is a rolling sum of fees, and both are per protocol and token:
CREATE MATERIALIZED VIEW protocol_tvl AS
SELECT protocol_id, token_symbol,
SUM(CASE WHEN event_type IN ('Deposit', 'Borrow') THEN amount_usd ELSE 0 END)
- SUM(CASE WHEN event_type IN ('Withdraw', 'Repay') THEN amount_usd ELSE 0 END) AS tvl_usd,
COUNT(DISTINCT user_address) AS unique_users,
MAX(block_ts) AS last_event_ts
FROM protocol_events
GROUP BY protocol_id, token_symbol;
CREATE MATERIALIZED VIEW protocol_revenue_24h AS
SELECT protocol_id, SUM(fee_usd) AS revenue_usd_24h, COUNT(*) AS event_count_24h
FROM protocol_events
WHERE block_ts > NOW() - INTERVAL '24 HOURS' AND fee_usd > 0
GROUP BY protocol_id;
CREATE MATERIALIZED VIEW tvl_change_alerts AS
SELECT protocol_id, token_symbol, event_type, user_address, amount_usd, block_ts,
CASE WHEN amount_usd > 1000000 AND event_type = 'Withdraw' THEN 'LARGE_WITHDRAWAL'
WHEN amount_usd > 5000000 AND event_type = 'Deposit' THEN 'LARGE_DEPOSIT'
ELSE 'NOTABLE_FLOW' END AS alert_type
FROM protocol_events
WHERE block_ts > NOW() - INTERVAL '5 MINUTES' AND amount_usd > 500000;
A live leaderboard is a join of protocol_tvl and protocol_revenue_24h ordered by TVL. If a dashboard shows a 15 percent APY that was true two hours ago and is now 8 percent, users allocate capital on a false premise; these views remove that gap.
Trading Desk P&L: Positions, Mark to Market, and Rollups
P&L is a join problem: live positions against streaming prices, with cost basis applied and results rolled up from trader to desk to book. Net positions come from fills, the latest price per instrument comes from DISTINCT ON, and the join between them is the mark-to-market view that recomputes only the affected rows on each tick:
CREATE SOURCE trade_fills (
trade_id VARCHAR,
instrument_id VARCHAR,
desk_id VARCHAR,
trader_id VARCHAR,
side VARCHAR, -- 'BUY' or 'SELL'
quantity DECIMAL(18,6),
exec_price DECIMAL(18,6),
exec_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'trade-fills',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;
CREATE SOURCE market_prices (
instrument_id VARCHAR,
mid_price DECIMAL(18,6),
price_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'market-prices',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;
CREATE MATERIALIZED VIEW net_positions AS
SELECT instrument_id, desk_id,
SUM(CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END) AS net_quantity,
SUM(CASE WHEN side = 'BUY' THEN quantity * exec_price ELSE -quantity * exec_price END) AS net_cost_basis,
MAX(exec_time) AS last_trade_time
FROM trade_fills
GROUP BY instrument_id, desk_id;
CREATE MATERIALIZED VIEW latest_prices AS
SELECT DISTINCT ON (instrument_id) instrument_id, mid_price, price_time
FROM market_prices
ORDER BY instrument_id, price_time DESC;
CREATE MATERIALIZED VIEW desk_pnl AS
SELECT np.desk_id, np.instrument_id, np.net_quantity, np.net_cost_basis,
lp.mid_price AS current_price,
np.net_quantity * lp.mid_price AS market_value,
np.net_quantity * lp.mid_price - np.net_cost_basis AS unrealized_pnl,
np.last_trade_time,
lp.price_time AS price_as_of
FROM net_positions np
JOIN latest_prices lp USING (instrument_id);
CREATE MATERIALIZED VIEW desk_pnl_summary AS
SELECT desk_id,
COUNT(DISTINCT instrument_id) AS open_positions,
SUM(market_value) AS total_market_value,
SUM(unrealized_pnl) AS total_unrealized_pnl,
SUM(unrealized_pnl) FILTER (WHERE unrealized_pnl < 0) AS total_losing_pnl,
SUM(unrealized_pnl) FILTER (WHERE unrealized_pnl > 0) AS total_winning_pnl,
MAX(price_as_of) AS data_freshness
FROM desk_pnl
GROUP BY desk_id;
Replaying Kafka from the earliest offset rebuilds positions from trade history before the views go live. Late exchange confirmations are reprocessed into the views as they arrive. The data_freshness column is the staleness monitor: a desk whose freshest price is more than 30 seconds old has a feed problem, not a P&L problem, and the dashboard should say so.
Serving and Distribution
Dashboards, risk tools, and quant research query the views over PostgreSQL on port 4566, and because the aggregates are precomputed the queries return in milliseconds regardless of how many events have been processed:
-- Most active pairs in the latest minute across venues
SELECT exchange, trading_pair, total_volume, vwap
FROM vwap_and_volatility
WHERE window_end = (SELECT MAX(window_end) FROM vwap_and_volatility)
ORDER BY total_volume DESC LIMIT 10;
-- Where to route a large WETH swap right now
SELECT protocol, pool_address, p95_swap_size_usd, avg_impact_coefficient, total_volume_usd_1h
FROM pool_impact_stats
WHERE symbol_in = 'WETH' OR symbol_out = 'WETH'
ORDER BY avg_impact_coefficient ASC LIMIT 5;
Kafka sinks distribute derived data to other systems: spread and impact alerts to trading desks, VWAP to execution algorithms, DeFi flow alerts to protocol teams.
CREATE SINK spread_alert_sink FROM spread_alerts
WITH (connector = 'kafka', topic = 'crypto.alerts.spread', properties.bootstrap.server = 'kafka:9092')
FORMAT PLAIN ENCODE JSON;
CREATE SINK impact_alert_sink FROM price_impact_alerts
WITH (connector = 'kafka', topic = 'alerts.dex.price-impact', properties.bootstrap.server = 'kafka:9092')
FORMAT PLAIN ENCODE JSON;
CREATE SINK vwap_sink FROM vwap_and_volatility
WITH (connector = 'kafka', topic = 'market.derived.vwap', properties.bootstrap.server = 'kafka:9092')
FORMAT PLAIN ENCODE JSON;
Batch Snapshots vs Streaming SQL for Market Analytics
| Metric | Batch or cached snapshot | Streaming SQL (RisingWave) |
|---|---|---|
| Order book depth and spread | Periodic snapshot | Live, every order event |
| Candles and VWAP | Scheduled job | Tumbling and hopping windows, continuous |
| Cross-venue divergence | Minutes late | Five-second slide, alert within the window |
| DEX volume and fee tier share | Daily or hourly | Rolling one-hour and 24-hour, per swap |
| Price impact | Historical | Per swap against a live pool coefficient |
| Protocol TVL and revenue | Cached query | Running sums, every block |
| Desk P&L | End of day or hourly | Every price tick, only affected rows recomputed |
| Serving | Separate cache | PostgreSQL protocol, or Kafka sinks |
Frequently Asked Questions
How do I keep the order book view correct when orders are cancelled or filled?
Model the order stream as an upsert keyed by order_id, or build a view that keeps the latest event per order with DISTINCT ON (order_id) ... ORDER BY order_id, event_time DESC and filter on remaining quantity there. The depth view then reflects the current state of every order.
Can RisingWave keep up with thousands of order book updates per second per market? Yes. Incremental maintenance touches only the affected price level, and compute scales horizontally with state in S3. Partition Kafka topics by market so the hot markets spread across nodes.
How do I compute open and close in a candle?
Use FIRST_VALUE(price) OVER (PARTITION BY market_id, window_start ORDER BY trade_time) and LAST_VALUE over the tumbled stream, alongside the aggregate columns. VWAP is SUM(price * quantity) / SUM(quantity) in the same view.
Where do USD amounts and price impact for swaps come from? From the decoding step upstream of Kafka. An indexer enriches each swap with oracle prices and the pool's pre- and post-swap price. RisingWave aggregates the decoded events; it does not need to run the AMM math itself.
What about realized P&L and cost basis methods?
net_positions tracks average cost. For FIFO or LIFO lot accounting, emit lot-level fills from the order management system and aggregate realized P&L per lot in a separate view; the mark-to-market join stays the same.
