SQL-FRONTIER Benchmark Deep-Dive / Temporal & Time-Series / task_022_Q22_linear_interpolation_missing_points
← Prev: Continuous Asset Lease Un... ⊞ All 120 Queries Next: Outlier-Resistant Rolling... →
✔ PASS Temporal & Time-Series task_022_Q22_linear_interpolation_missing_points Scaled: 1M+ Records Ordered: Yes

Bi-directional Linear Value Interpolation between Observations

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

When a sensor drops offline for 30 minutes, data scientists prefer linear interpolation between the last known reading and the next recovered reading rather than flat zero-filling.

⚡ The Frontier Trap (Why LLMs Fail)

Requires simultaneous look-behind (LAG) and look-ahead (LEAD) across varying time distances.

Prompt / User Request Given to Model
When a sensor drops offline for 30 minutes, data scientists prefer linear interpolation between the last known reading and the next recovered reading rather than flat zero-filling.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: ts, raw_psi, interpolated_psi.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 pressure_readings 1,000,000 rows 15.3 MiB Data Volume
ts (TIMESTAMP) psi (DOUBLE)
Data Preview (showing sample rows from 1,000,000 total rows):
# ts psi
1 2026-03-01 10:00:00 100
2 2026-03-01 10:01:00 NULL
3 2026-03-01 10:02:00 NULL
4 2026-03-01 10:03:00 130
5 2026-03-01 09:30:00.000009 90.39
6 2026-03-01 09:30:00.000026 98.86
7 2026-03-01 09:30:00.000046 131.74
8 2026-03-01 09:30:00.000061 117.3
9 2026-03-01 09:30:00.000080 114.86
10 2026-03-01 09:30:00.000104 125.6
11 2026-03-01 09:30:00.000130 88.5
12 2026-03-01 09:30:00.000145 100.74
13 2026-03-01 09:30:00.000168 93.37
14 2026-03-01 09:30:00.000185 128.37
15 2026-03-01 09:30:00.000206 97.81

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: 310.93 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 🔥 22.0% Cost
Latency: 68.35 ms
Rows In/Out: 1,000,000
Data Vol: 22.89 MB
⚠️ Heavy Execution Bottleneck (22.0% of query runtime)
📐 PROJECTION 0.1%
Latency: 0.46 ms
Rows In/Out: 1,000,000
Data Vol: 22.89 MB
📁 Projections: ['ts', 'raw_psi', '...
📐 PROJECTION 0.1%
Latency: 0.19 ms
Rows In/Out: 1,000,000
Data Vol: 45.78 MB
📁 Projections: ['#0', '#1', '#2', ...
🪟 WINDOW 🔥 77.2% Cost
Latency: 239.95 ms
Rows In/Out: 1,000,000
Data Vol: 45.78 MB
📁 Projections: ['last_value(psi IG...
⚠️ Heavy Execution Bottleneck (77.2% of query runtime)
🔍 TABLE_SCAN 0.6%
Latency: 1.97 ms
Rows In/Out: 1,000,000
Data Vol: 15.26 MB
📁 memory.main.pressure_readings
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
WINDOW 77.2% 239.95 ms 1,000,000 45.78 MB Projections: ['last_value(psi IGNORE NULLS) OVER 🔥 BOTTLENECK
ORDER_BY 22.0% 68.35 ms 1,000,000 22.89 MB - 🔥 BOTTLENECK
TABLE_SCAN 0.6% 1.97 ms 1,000,000 15.26 MB memory.main.pressure_readings Normal
PROJECTION 0.1% 0.46 ms 1,000,000 22.89 MB Projections: ['ts', 'raw_psi', 'interpolated_psi Normal
PROJECTION 0.1% 0.19 ms 1,000,000 45.78 MB Projections: ['#0', '#1', '#2', '#3', '#4', '#5' 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: 161.66 ms) 
WITH bounds AS
  (SELECT ts,
          psi,
          last_value(psi
                     IGNORE nulls) OVER (
                                         ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_psi,
          last_value(ts
                     IGNORE nulls) OVER (
                                         ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_ts,
          first_value(psi
                      IGNORE nulls) OVER (
                                          ORDER BY ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_psi,
          first_value(ts
                      IGNORE nulls) OVER (
                                          ORDER BY ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_ts
   FROM pressure_readings)
SELECT ts,
       psi AS raw_psi,
       CASE
           WHEN psi IS NOT NULL THEN psi
           ELSE round(prev_psi + (next_psi - prev_psi) * (date_diff('second', prev_ts, ts) * 1.0 / date_diff('second', prev_ts, next_ts)), 2)
       END AS interpolated_psi
FROM bounds
ORDER BY ts;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 124.17 ms) 
-- Linearly interpolate missing PSI values based on preceding and following known points
 WITH bounds AS
  (SELECT ts,
          psi,
          last_value(psi
                     IGNORE nulls) OVER (
                                         ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_psi,
          last_value(ts
                     IGNORE nulls) OVER (
                                         ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_ts,
          first_value(psi
                      IGNORE nulls) OVER (
                                          ORDER BY ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_psi,
          first_value(ts
                      IGNORE nulls) OVER (
                                          ORDER BY ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_ts
   FROM pressure_readings)
SELECT ts,
       psi AS raw_psi,
       CASE
           WHEN psi IS NOT NULL THEN psi
           ELSE round(prev_psi + (next_psi - prev_psi) * (date_diff('second', prev_ts, ts) * 1.0 / date_diff('second', prev_ts, next_ts)), 2)
       END AS interpolated_psi
FROM bounds
ORDER BY ts;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE pressure_readings (ts TIMESTAMP, psi DOUBLE);


INSERT INTO pressure_readings
VALUES ('2026-03-01 10:00:00', 100.0),
       ('2026-03-01 10:01:00', NULL),
       ('2026-03-01 10:02:00', NULL),
       ('2026-03-01 10:03:00', 130.0);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (1,000,000 rows × 3 cols)
# ts raw_psi interpolated_psi
1 2026-03-01 09:30:00.000009 90.39 90.39
2 2026-03-01 09:30:00.000026 98.86 98.86
3 2026-03-01 09:30:00.000046 131.74 131.74
4 2026-03-01 09:30:00.000061 117.3 117.3
5 2026-03-01 09:30:00.000080 114.86 114.86
6 2026-03-01 09:30:00.000104 125.6 125.6
7 2026-03-01 09:30:00.000130 88.5 88.5
8 2026-03-01 09:30:00.000145 100.74 100.74
9 2026-03-01 09:30:00.000168 93.37 93.37
10 2026-03-01 09:30:00.000185 128.37 128.37
11 2026-03-01 09:30:00.000206 97.81 97.81
12 2026-03-01 09:30:00.000224 104.94 104.94
13 2026-03-01 09:30:00.000241 117.94 117.94
14 2026-03-01 09:30:00.000261 111.3 111.3
15 2026-03-01 09:30:00.000282 110.76 110.76
16 2026-03-01 09:30:00.000305 100.47 100.47
17 2026-03-01 09:30:00.000322 120.44 120.44
18 2026-03-01 09:30:00.000342 108.85 108.85
19 2026-03-01 09:30:00.000376 157.71 157.71
20 2026-03-01 09:30:00.000383 123.59 123.59
21 2026-03-01 09:30:00.000412 82.01 82.01
22 2026-03-01 09:30:00.000421 118.43 118.43
23 2026-03-01 09:30:00.000447 134.35 134.35
24 2026-03-01 09:30:00.000465 128.61 128.61
25 2026-03-01 09:30:00.000489 91.86 91.86
Showing first 25 of 1,000,000 total rows.
Expected Golden Output (1,000,000 rows × 3 cols)
# ts raw_psi interpolated_psi
1 2026-03-01 09:30:00.000009 90.39 90.39
2 2026-03-01 09:30:00.000026 98.86 98.86
3 2026-03-01 09:30:00.000046 131.74 131.74
4 2026-03-01 09:30:00.000061 117.3 117.3
5 2026-03-01 09:30:00.000080 114.86 114.86
6 2026-03-01 09:30:00.000104 125.6 125.6
7 2026-03-01 09:30:00.000130 88.5 88.5
8 2026-03-01 09:30:00.000145 100.74 100.74
9 2026-03-01 09:30:00.000168 93.37 93.37
10 2026-03-01 09:30:00.000185 128.37 128.37
11 2026-03-01 09:30:00.000206 97.81 97.81
12 2026-03-01 09:30:00.000224 104.94 104.94
13 2026-03-01 09:30:00.000241 117.94 117.94
14 2026-03-01 09:30:00.000261 111.3 111.3
15 2026-03-01 09:30:00.000282 110.76 110.76
16 2026-03-01 09:30:00.000305 100.47 100.47
17 2026-03-01 09:30:00.000322 120.44 120.44
18 2026-03-01 09:30:00.000342 108.85 108.85
19 2026-03-01 09:30:00.000376 157.71 157.71
20 2026-03-01 09:30:00.000383 123.59 123.59
21 2026-03-01 09:30:00.000412 82.01 82.01
22 2026-03-01 09:30:00.000421 118.43 118.43
23 2026-03-01 09:30:00.000447 134.35 134.35
24 2026-03-01 09:30:00.000465 128.61 128.61
25 2026-03-01 09:30:00.000489 91.86 91.86
Showing first 25 of 1,000,000 total rows.
← Prev: Continuous Asset Lease Un... ⊞ Back to Benchmark Dashboard Next: Outlier-Resistant Rolling... →