Given two large, unsorted datasets representing order books and trade logs, perform an efficient join to align trades with the prevailing market state at the millisecond level for Jane Street’s market data.
Return the latest order-book snapshot for the same symbol whose timestamp is at or before each trade. Include trades without a prior snapshot, and use the book identifier to break timestamp ties.
trade_id, symbol, trade_ts, trade_price, quantity, side, state_ts, bid_price, bid_size, ask_price, ask_sizetrade_ts ascending, then trade_id ascending| Column | Type | Description |
|---|---|---|
| book_idPK | INTEGER | Unique order-book snapshot identifier |
| symbol | VARCHAR(16) | Traded instrument symbol |
| book_ts | TIMESTAMP(3) | Snapshot timestamp with millisecond precision |
| bid_price | NUMERIC(12,4) | Best bid price |
| bid_size | INTEGER | Quantity available at the best bid |
| ask_price | NUMERIC(12,4) | Best ask price |
| ask_size | INTEGER | Quantity available at the best ask |
| Column | Type | Description |
|---|---|---|
| trade_idPK | INTEGER | Unique trade identifier |
| symbol | VARCHAR(16) | Traded instrument symbol |
| trade_ts | TIMESTAMP(3) | Trade execution timestamp with millisecond precision |
| trade_price | NUMERIC(12,4) | Execution price |
| quantity | INTEGER | Executed quantity |
| side | VARCHAR(16) | Trade aggressor side |