✔ PASS Warehouse & Statistical task_095_Q95_robust_z_score_iqr_normalization Scaled: 1M+ Records Ordered: Yes

Robust Z-Score Normalization using Median and Interquartile Range (IQR)

Candidate Latency (1M Rows)

129.76 ms

(1.0x faster than golden)

Golden Latency (1M Rows)

132.21 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

11.4 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 16.0s

✔ 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

Standardizing skewed metrics (like API response latency) for machine learning feature pipelines requires scaling by IQR (Q3 - Q1) rather than standard deviation.

⚡ The Frontier Trap (Why LLMs Fail)

Standard Z-score produces biased features when distributions have heavy tails.

Prompt / User Request Given to Model

Standardizing skewed metrics (like API response latency) for machine learning feature pipelines requires scaling by IQR (Q3 - Q1) rather than standard deviation.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: request_id, latency_ms, robust_iqr_z_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 response_times 1,000,000 rows 11.4 MiB Data Volume

request_id (INTEGER) latency_ms (DOUBLE)

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

#	request_id	latency_ms
1	1	45
2	2	50
3	3	52
4	4	48
5	5	300
6	5	320.9
7	6	90.05
8	7	24.66
9	8	130.67
10	9	238.68
11	10	108.3
12	11	76.63
13	12	67.08
14	13	0.01
15	14	119.83

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: 266.32 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

⏳ ORDER_BY 🔥 51.4% Cost

Latency: 136.86 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📐 PROJECTION 16.3%

Latency: 43.42 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: ['request_id', 'lat...

📐 PROJECTION 0.2%

Latency: 0.51 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📁 Projections: ['request_id', 'lat...

⚙️ CROSS_PRODUCT 0.5%

Latency: 1.26 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

🔍 TABLE_SCAN 0.5%

Latency: 1.39 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 memory.main.response_times

📊 UNGROUPED_AGGREGATE 🔥 30.8% Cost

Latency: 81.93 ms

Rows In/Out: 1

Data Vol: 24 B

📐 PROJECTION 0.1%

Latency: 0.27 ms

Rows In/Out: 1,000,000

Data Vol: 45.78 MB

📁 Projections: ['latency_ms', '0.2...

🔍 TABLE_SCAN 0.3%

Latency: 0.68 ms

Rows In/Out: 1,000,000

Data Vol: 7.63 MB

📁 memory.main.response_times

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
ORDER_BY	51.4%	136.86 ms	1,000,000	19.07 MB	-	🔥 BOTTLENECK
UNGROUPED_AGGREGATE	30.8%	81.93 ms	1	0.00 MB	-	🔥 BOTTLENECK
PROJECTION	16.3%	43.42 ms	1,000,000	19.07 MB	Projections: ['request_id', 'latency_ms', 'robus	Normal
TABLE_SCAN	0.5%	1.39 ms	1,000,000	11.44 MB	memory.main.response_times	Normal
CROSS_PRODUCT	0.5%	1.26 ms	1,000,000	34.33 MB	-	Normal
TABLE_SCAN	0.3%	0.68 ms	1,000,000	7.63 MB	memory.main.response_times	Normal
PROJECTION	0.2%	0.51 ms	1,000,000	26.70 MB	Projections: ['request_id', 'latency_ms', 'media	Normal
PROJECTION	0.1%	0.27 ms	1,000,000	45.78 MB	Projections: ['latency_ms', '0.25', 'latency_ms'	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH quartiles AS
  (SELECT quantile_cont(latency_ms, 0.25) AS q1,
          quantile_cont(latency_ms, 0.50) AS median_val,
          quantile_cont(latency_ms, 0.75) AS q3
   FROM response_times)
SELECT r.request_id,
       r.latency_ms,
       round((r.latency_ms - q.median_val) / nullif(q.q3 - q.q1, 0), 3) AS robust_iqr_z_score
FROM response_times r
CROSS JOIN quartiles q
ORDER BY robust_iqr_z_score DESC;

-- Compute robust IQR-normalized score: (x - Q2) / (Q3 - Q1)
 WITH quartiles AS
  (SELECT quantile_cont(latency_ms, 0.25) AS q1,
          quantile_cont(latency_ms, 0.50) AS median_val,
          quantile_cont(latency_ms, 0.75) AS q3
   FROM response_times)
SELECT r.request_id,
       r.latency_ms,
       round((r.latency_ms - q.median_val) / nullif(q.q3 - q.q1, 0), 3) AS robust_iqr_z_score
FROM response_times r
CROSS JOIN quartiles q
ORDER BY robust_iqr_z_score DESC;

CREATE TABLE response_times (request_id INT, latency_ms DOUBLE);


INSERT INTO response_times
VALUES (1, 45.0),
       (2, 50.0),
       (3, 52.0),
       (4, 48.0),
       (5, 300.0);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1,000,000 rows × 3 cols)

#	request_id	latency_ms	robust_iqr_z_score
1	485,101	707.37	4.018
2	664,953	622.79	3.46
3	104,977	611.15	3.383
4	649,516	606.75	3.354
5	168,017	603.15	3.33
6	191,422	601.59	3.32
7	781,808	595.95	3.282
8	402,057	593.86	3.268
9	55,184	593.39	3.265
10	969,896	592.09	3.257
11	674,164	592.19	3.257
12	126,301	585.41	3.213
13	194,954	585.51	3.213
14	499,773	584.81	3.209
15	457,310	583.83	3.202
16	169,262	580.12	3.178
17	639,141	569.24	3.106
18	111,835	569.13	3.105
19	189,881	565.99	3.084
20	473,516	563.24	3.066
21	352,959	562.77	3.063
22	556,182	562.8	3.063
23	974,103	560.67	3.049
24	575,621	560.34	3.047
25	697,981	558.92	3.038

Showing first 25 of 1,000,000 total rows.

Expected Golden Output (1,000,000 rows × 3 cols)

#	request_id	latency_ms	robust_iqr_z_score
1	485,101	707.37	4.018
2	664,953	622.79	3.46
3	104,977	611.15	3.383
4	649,516	606.75	3.354
5	168,017	603.15	3.33
6	191,422	601.59	3.32
7	781,808	595.95	3.282
8	402,057	593.86	3.268
9	55,184	593.39	3.265
10	674,164	592.19	3.257
11	969,896	592.09	3.257
12	126,301	585.41	3.213
13	194,954	585.51	3.213
14	499,773	584.81	3.209
15	457,310	583.83	3.202
16	169,262	580.12	3.178
17	639,141	569.24	3.106
18	111,835	569.13	3.105
19	189,881	565.99	3.084
20	473,516	563.24	3.066
21	352,959	562.77	3.063
22	556,182	562.8	3.063
23	974,103	560.67	3.049
24	575,621	560.34	3.047
25	697,981	558.92	3.038

Showing first 25 of 1,000,000 total rows.

← Prev: Median Absolute Deviation... ⊞ Back to Benchmark Dashboard Next: Financial Ledger Running ... →