✔ PASS Temporal & Time-Series task_013_Q13_order_book_snapshot_reconstruction Scaled: 1M+ Records Ordered: Yes

Point-in-Time Limit Order Book (LOB) State Reconstruction

Candidate Latency (1M Rows)

18.91 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

17.93 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

64.8 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 16.4s

✔ Correctness Verified on 1M+ Records

Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap

Business Scenario & Problem

Market microstructure researchers need to reconstruct the exact bids and asks available at an arbitrary point in time from an append-only event stream of place, cancel, and fill orders.

⚡ The Frontier Trap (Why LLMs Fail)

Summing active orders without filtering out cancellations that occurred before the target time produces a phantom order book.

Prompt / User Request Given to Model

Market microstructure researchers need to reconstruct the exact bids and asks available at an arbitrary point in time from an append-only event stream of place, cancel, and fill orders.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: side, price, aggregate_depth.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 64.8 MiB)

Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:

📊 order_events 1,000,000 rows 64.8 MiB Data Volume

event_id (INTEGER) order_id (INTEGER) event_type (VARCHAR) side (VARCHAR) price (DOUBLE) quantity (INTEGER) event_time (TIMESTAMP)

Data Preview (showing sample rows from 1,000,000 total rows):

#	event_id	order_id	event_type	side	price	quantity	event_time
1	1	101	INSERT	BID	100.5	10	2026-03-01 09:30:01
2	2	102	INSERT	BID	100.25	20	2026-03-01 09:30:02
3	3	101	CANCEL	BID	100.5	10	2026-03-01 09:30:05
4	4	103	INSERT	BID	100.6	15	2026-03-01 09:30:06
5	4	103	click	BID	100.34	19	2026-03-01 09:30:00.000006
6	5	104	view	BID	100.36	7	2026-03-01 09:30:00.000025
7	6	105	add_to_cart	BID	100.62	21	2026-03-01 09:30:00.000048
8	7	106	checkout	BID	100.34	5	2026-03-01 09:30:00.000067
9	8	107	search	BID	100.4	8	2026-03-01 09:30:00.000083
10	9	108	click	BID	100.89	19	2026-03-01 09:30:00.000123
11	10	109	view	BID	100.42	13	2026-03-01 09:30:00.000122
12	11	110	add_to_cart	BID	100.62	18	2026-03-01 09:30:00.000148
13	12	111	checkout	BID	100.52	12	2026-03-01 09:30:00.000163
14	13	112	search	BID	100.4	14	2026-03-01 09:30:00.000183
15	14	113	click	BID	100.55	16	2026-03-01 09:30:00.000205

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 53.62 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 40 B

📁 Projections: ['__internal_decomp...

⏳ ORDER_BY 0.1%

Latency: 0.07 ms

Rows In/Out: 1

Data Vol: 20 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 20 B

📁 Projections: ['__internal_compre...

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 40 B

📁 Projections: ['__internal_decomp...

📊 HASH_GROUP_BY 0.1%

Latency: 0.08 ms

Rows In/Out: 1

Data Vol: 28 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 28 B

📁 Projections: ['side', 'price', '...

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 28 B

📁 Projections: ['__internal_compre...

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 40 B

📁 Projections: ['#1', '#2', '#3']

🎯 FILTER 1.1%

Latency: 0.58 ms

Rows In/Out: 1

Data Vol: 44 B

📐 PROJECTION 0.5%

Latency: 0.26 ms

Rows In/Out: 250,002

Data Vol: 10.49 MB

📁 Projections: ['#0', '__internal_...

📊 HASH_GROUP_BY 🔥 82.0% Cost

Latency: 43.97 ms

Rows In/Out: 250,002

Data Vol: 7.63 MB

📐 PROJECTION 1.7%

Latency: 0.90 ms

Rows In/Out: 250,003

Data Vol: 4.77 MB

📁 Projections: ['order_id', 'side'...

📐 PROJECTION 1.1%

Latency: 0.60 ms

Rows In/Out: 250,003

Data Vol: 8.58 MB

📁 Projections: ['#0', '#1', '__int...

🔍 TABLE_SCAN 13.3%

Latency: 7.14 ms

Rows In/Out: 250,003

Data Vol: 11.44 MB

📁 memory.main.order_events

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_GROUP_BY	82.0%	43.97 ms	250,002	7.63 MB	-	🔥 BOTTLENECK
TABLE_SCAN	13.3%	7.14 ms	250,003	11.44 MB	memory.main.order_events	Normal
PROJECTION	1.7%	0.90 ms	250,003	4.77 MB	Projections: ['order_id', 'side', 'price', "CASE	Normal
PROJECTION	1.1%	0.60 ms	250,003	8.58 MB	Projections: ['#0', '#1', '__internal_compress_s	Normal
FILTER	1.1%	0.58 ms	1	0.00 MB	-	Normal
PROJECTION	0.5%	0.26 ms	250,002	10.49 MB	Projections: ['#0', '__internal_decompress_strin	Normal
HASH_GROUP_BY	0.1%	0.08 ms	1	0.00 MB	-	Normal
ORDER_BY	0.1%	0.07 ms	1	0.00 MB	-	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: ['__internal_compress_string_uinteg	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: ['__internal_decompress_string(#0)'	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: ['__internal_compress_string_uinteg	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: ['__internal_decompress_string(#0)'	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: ['#1', '#2', '#3']	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: ['side', 'price', 'remaining_qty']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- Reconstruct order book state at exactly 09:30:05
WITH snapshot_events AS
  (SELECT *
   FROM order_events
   WHERE event_time <= TIMESTAMP '2026-03-01 09:30:05'),
     active_orders AS
  (SELECT order_id,
          side,
          price, -- Net quantity remaining after cancellations/fills
 sum(CASE
         WHEN event_type = 'INSERT' THEN quantity
         ELSE -quantity
     END) AS remaining_qty
   FROM snapshot_events
   GROUP BY order_id,
            side,
            price
   HAVING sum(CASE
                  WHEN event_type = 'INSERT' THEN quantity
                  ELSE -quantity
              END) > 0)
SELECT side,
       price,
       sum(remaining_qty) AS aggregate_depth
FROM active_orders
GROUP BY side,
         price
ORDER BY price DESC;

-- Reconstruct order book state at exactly 09:30:05
 WITH snapshot_events AS
  (SELECT *
   FROM order_events
   WHERE event_time <= TIMESTAMP '2026-03-01 09:30:05'),
      active_orders AS
  (SELECT order_id,
          side,
          price, -- Net quantity remaining after cancellations/fills
 sum(CASE
         WHEN event_type = 'INSERT' THEN quantity
         ELSE -quantity
     END) AS remaining_qty
   FROM snapshot_events
   GROUP BY order_id,
            side,
            price
   HAVING sum(CASE
                  WHEN event_type = 'INSERT' THEN quantity
                  ELSE -quantity
              END) > 0)
SELECT side,
       price,
       sum(remaining_qty) AS aggregate_depth
FROM active_orders
GROUP BY side,
         price
ORDER BY price DESC;

CREATE TABLE order_events (event_id INT, order_id INT, event_type VARCHAR, -- 'INSERT', 'CANCEL', 'FILL'
 side VARCHAR, -- 'BID', 'ASK'
 price DOUBLE,
       quantity INT, event_time TIMESTAMP);


INSERT INTO order_events
VALUES (1, 101, 'INSERT', 'BID', 100.50, 10, '2026-03-01 09:30:01'),
       (2, 102, 'INSERT', 'BID', 100.25, 20, '2026-03-01 09:30:02'),
       (3, 101, 'CANCEL', 'BID', 100.50, 10, '2026-03-01 09:30:05'),
       (4, 103, 'INSERT', 'BID', 100.60, 15, '2026-03-01 09:30:06');

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1 rows × 3 cols)

#	side	price	aggregate_depth
1	BID	100.25	20

Expected Golden Output (1 rows × 3 cols)

#	side	price	aggregate_depth
1	BID	100.25	20

← Prev: Inter-Packet Network Arri... ⊞ Back to Benchmark Dashboard Next: Time-Weighted Average Pri... →