SQL-FRONTIER Benchmark Deep-Dive / Temporal & Time-Series / task_014_Q14_time_weighted_average_price_twap
← Prev: Point-in-Time Limit Order... ⊞ All 120 Queries Next: Intraday Volume-Weighted ... →
✔ PASS Temporal & Time-Series task_014_Q14_time_weighted_average_price_twap Scaled: 1M+ Records Ordered: Yes

Time-Weighted Average Price (TWAP) with Variable Interval Weights

Candidate Latency (1M Rows)
92.05 ms
(1.1x slower than golden)
Golden Latency (1M Rows)
83.52 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
34.3 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 16.2s
✔ 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

TWAP measures the average price of an asset weighted by the duration of time that each price level was sustained, preventing brief price spikes from distorting valuation.

⚡ The Frontier Trap (Why LLMs Fail)

Taking simple AVG(price) biases the result towards periods with frequent tick updates rather than sustained duration.

Prompt / User Request Given to Model
TWAP measures the average price of an asset weighted by the duration of time that each price level was sustained, preventing brief price spikes from distorting valuation.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: symbol, twap_price, total_duration_seconds.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 price_ticks 1,000,000 rows 34.3 MiB Data Volume
tick_time (TIMESTAMP) symbol (VARCHAR) price (DOUBLE)
Data Preview (showing sample rows from 1,000,000 total rows):
# tick_time symbol price
1 2026-03-01 10:00:00 ETH 3,000
2 2026-03-01 10:10:00 ETH 3,050
3 2026-03-01 10:30:00 ETH 3,010
4 2026-03-01 11:00:00 ETH 3,010
5 2026-03-01 09:30:00 NVDA 3,016.35
6 2026-03-01 09:30:00.000023 AAPL 3,025.01
7 2026-03-01 09:30:00.000046 MSFT 3,001.28
8 2026-03-01 09:30:00.000070 GOOGL 3,045.73
9 2026-03-01 09:30:00.000083 AMZN 3,026.91
10 2026-03-01 09:30:00.000106 TSLA 3,034.18
11 2026-03-01 09:30:00.000131 META 2,985.65
12 2026-03-01 09:30:00.000154 AMD 2,977.61
13 2026-03-01 09:30:00.000173 NVDA 2,982.28
14 2026-03-01 09:30:00.000181 AAPL 3,016.04
15 2026-03-01 09:30:00.000203 MSFT 3,007.82

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: 338.98 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 9
Data Vol: 360 B
📁 Projections: ['__internal_decomp...
⏳ ORDER_BY 0.0%
Latency: 0.17 ms
Rows In/Out: 9
Data Vol: 288 B
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 9
Data Vol: 288 B
📁 Projections: ['__internal_compre...
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 9
Data Vol: 360 B
📁 Projections: ['symbol', 'twap_pr...
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 9
Data Vol: 360 B
📁 Projections: ['__internal_decomp...
📊 HASH_GROUP_BY 3.6%
Latency: 12.18 ms
Rows In/Out: 9
Data Vol: 288 B
📐 PROJECTION 0.6%
Latency: 2.07 ms
Rows In/Out: 999,991
Data Vol: 22.89 MB
📁 Projections: ['symbol', '(price ...
📐 PROJECTION 1.4%
Latency: 4.59 ms
Rows In/Out: 999,991
Data Vol: 22.89 MB
📁 Projections: ['__internal_compre...
📐 PROJECTION 1.8%
Latency: 6.16 ms
Rows In/Out: 999,991
Data Vol: 30.52 MB
📁 Projections: ['symbol', 'price',...
🎯 FILTER 1.4%
Latency: 4.63 ms
Rows In/Out: 999,991
Data Vol: 38.15 MB
📐 PROJECTION 0.0%
Latency: 0.15 ms
Rows In/Out: 1,000,000
Data Vol: 38.15 MB
📁 Projections: ['#0', '#1', '#2', ...
🪟 WINDOW 🔥 87.2% Cost
Latency: 295.44 ms
Rows In/Out: 1,000,000
Data Vol: 38.15 MB
📁 Projections: lead(tick_time, 1, ...
⚠️ Heavy Execution Bottleneck (87.2% of query runtime)
🔍 TABLE_SCAN 4.0%
Latency: 13.56 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
📁 memory.main.price_ticks
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
WINDOW 87.2% 295.44 ms 1,000,000 38.15 MB Projections: lead(tick_time, 1, NULL::TIMESTAMP) 🔥 BOTTLENECK
TABLE_SCAN 4.0% 13.56 ms 1,000,000 30.52 MB memory.main.price_ticks Normal
HASH_GROUP_BY 3.6% 12.18 ms 9 0.00 MB - Normal
PROJECTION 1.8% 6.16 ms 999,991 30.52 MB Projections: ['symbol', 'price', 'duration_secon Normal
FILTER 1.4% 4.63 ms 999,991 38.15 MB - Normal
PROJECTION 1.4% 4.59 ms 999,991 22.89 MB Projections: ['__internal_compress_string_ubigin Normal
PROJECTION 0.6% 2.07 ms 999,991 22.89 MB Projections: ['symbol', '(price * CAST(duration_ Normal
ORDER_BY 0.0% 0.17 ms 9 0.00 MB - Normal
PROJECTION 0.0% 0.15 ms 1,000,000 38.15 MB Projections: ['#0', '#1', '#2', '#3'] Normal
PROJECTION 0.0% 0.01 ms 9 0.00 MB Projections: ['symbol', 'twap_price', 'total_dur Normal
PROJECTION 0.0% 0.01 ms 9 0.00 MB Projections: ['__internal_compress_string_ubigin Normal
PROJECTION 0.0% 0.01 ms 9 0.00 MB Projections: ['__internal_decompress_string(#0)' Normal
PROJECTION 0.0% 0.00 ms 9 0.00 MB Projections: ['__internal_decompress_string(#0)' Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 92.05 ms) 
WITH tick_durations AS
  (SELECT symbol,
          price,
          date_diff('second', tick_time, lead(tick_time) OVER (PARTITION BY symbol
                                                               ORDER BY tick_time)) AS duration_seconds
   FROM price_ticks)
SELECT symbol,
       sum(price * duration_seconds) / sum(duration_seconds) AS twap_price,
       sum(duration_seconds) AS total_duration_seconds
FROM tick_durations
WHERE duration_seconds IS NOT NULL
GROUP BY symbol
ORDER BY symbol;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 83.52 ms) 
-- Calculate 1-hour TWAP from 10:00 to 11:00
 WITH tick_durations AS
  (SELECT symbol,
          tick_time,
          price,
          date_diff('second', tick_time, lead(tick_time, 1, TIMESTAMP '2026-03-01 11:00:00') OVER (PARTITION BY symbol
                                                                                                   ORDER BY tick_time)) AS duration_seconds
   FROM price_ticks
   WHERE tick_time < TIMESTAMP '2026-03-01 11:00:00')
SELECT symbol,
       round(sum(price * duration_seconds) / sum(duration_seconds), 2) AS twap_price,
       sum(duration_seconds) AS total_duration_seconds
FROM tick_durations
GROUP BY symbol;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE price_ticks (tick_time TIMESTAMP, symbol VARCHAR, price DOUBLE);


INSERT INTO price_ticks
VALUES ('2026-03-01 10:00:00', 'ETH', 3000.00),
       -- lasted 10 min
 ('2026-03-01 10:10:00', 'ETH', 3050.00),
 -- lasted 20 min
 ('2026-03-01 10:30:00', 'ETH', 3010.00),
 -- lasted 30 min
 ('2026-03-01 11:00:00', 'ETH', 3010.00); -- window end

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (9 rows × 3 cols)
# symbol twap_price total_duration_seconds
1 AAPL 3,022.92 19
2 AMD 3,012.8674 19
3 AMZN 3,015.6463 19
4 ETH 3,021.6667 3,600
5 GOOGL 3,008.6105 19
6 META 3,024.28 19
7 MSFT 3,017.4953 19
8 NVDA 3,022.0326 19
9 TSLA 3,012.3863 19
Expected Golden Output (9 rows × 3 cols)
# symbol twap_price total_duration_seconds
1 AMD 3,028.89 5,400
2 MSFT 3,018.96 5,400
3 GOOGL 3,028.87 5,400
4 META 3,031.14 5,400
5 NVDA 3,041.03 5,400
6 ETH 3,021.67 3,600
7 AAPL 3,007.73 5,400
8 AMZN 3,021.11 5,400
9 TSLA 3,008.92 5,400
← Prev: Point-in-Time Limit Order... ⊞ Back to Benchmark Dashboard Next: Intraday Volume-Weighted ... →