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

Median Absolute Deviation (MAD) Robust Statistical Outlier Detection

Candidate Latency (1M Rows)

241.66 ms

(1.0x slower than golden)

Golden Latency (1M Rows)

239.83 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: 11.9s

✔ 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

Traditional standard deviation Z-scores fail in the presence of extreme outliers because the outliers themselves distort the mean and standard deviation. MAD is a robust statistical scale estimator.

⚡ The Frontier Trap (Why LLMs Fail)

Mean and standard deviation break down completely on fat-tailed or skewed data distributions.

Prompt / User Request Given to Model

Traditional standard deviation Z-scores fail in the presence of extreme outliers because the outliers themselves distort the mean and standard deviation. MAD is a robust statistical scale estimator.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: tx_id, amount, overall_median, robust_mad_score, anomaly_flag.
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:

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

tx_id (INTEGER) amount (DOUBLE)

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

#	tx_id	amount
1	1	10
2	2	12
3	3	11
4	4	13
5	5	12
6	6	1,000
7	6	372.88
8	7	301.72
9	8	0.01
10	9	300.15
11	10	32.47
12	11	1,397.29
13	12	0.01
14	13	0.01
15	14	305.26

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

🔄 CTE 9.9%

Latency: 29.32 ms

Rows In/Out: 0

Data Vol: 0 B

📐 PROJECTION 0.5%

Latency: 1.59 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📁 Projections: ['tx_id', 'amount',...

⚙️ CROSS_PRODUCT 0.3%

Latency: 0.74 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

🔍 TABLE_SCAN 0.4%

Latency: 1.23 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 memory.main.payment_volumes

📊 UNGROUPED_AGGREGATE 9.6%

Latency: 28.50 ms

Rows In/Out: 1

Data Vol: 8 B

📐 PROJECTION 0.0%

Latency: 0.06 ms

Rows In/Out: 1,000,000

Data Vol: 7.63 MB

📁 Projections: amount

🔍 TABLE_SCAN 0.2%

Latency: 0.61 ms

Rows In/Out: 1,000,000

Data Vol: 7.63 MB

📁 memory.main.payment_volumes

📐 PROJECTION 2.6%

Latency: 7.66 ms

Rows In/Out: 1,000,000

Data Vol: 41.96 MB

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

⏳ ORDER_BY 🔥 51.9% Cost

Latency: 153.33 ms

Rows In/Out: 1,000,000

Data Vol: 41.96 MB

📐 PROJECTION 3.3%

Latency: 9.73 ms

Rows In/Out: 1,000,000

Data Vol: 41.96 MB

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

📐 PROJECTION 10.2%

Latency: 30.08 ms

Rows In/Out: 1,000,000

Data Vol: 41.96 MB

📁 Projections: ['tx_id', 'amount',...

📐 PROJECTION 1.1%

Latency: 3.32 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📁 Projections: ['tx_id', 'amount',...

⚙️ CROSS_PRODUCT 0.3%

Latency: 0.88 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

🔍 CTE_SCAN 0.4%

Latency: 1.22 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📊 UNGROUPED_AGGREGATE 9.2%

Latency: 27.05 ms

Rows In/Out: 1

Data Vol: 8 B

📐 PROJECTION 0.0%

Latency: 0.06 ms

Rows In/Out: 1,000,000

Data Vol: 7.63 MB

📁 Projections: abs_deviation

🔍 CTE_SCAN 0.0%

Latency: 0.02 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
ORDER_BY	51.9%	153.33 ms	1,000,000	41.96 MB	-	🔥 BOTTLENECK
PROJECTION	10.2%	30.08 ms	1,000,000	41.96 MB	Projections: ['tx_id', 'amount', 'overall_median	Normal
CTE	9.9%	29.32 ms	0	0.00 MB	-	Normal
UNGROUPED_AGGREGATE	9.6%	28.50 ms	1	0.00 MB	-	Normal
UNGROUPED_AGGREGATE	9.2%	27.05 ms	1	0.00 MB	-	Normal
PROJECTION	3.3%	9.73 ms	1,000,000	41.96 MB	Projections: ['#0', '#1', '#2', '#3', '__interna	Normal
PROJECTION	2.6%	7.66 ms	1,000,000	41.96 MB	Projections: ['#0', '#1', '#2', '#3', '__interna	Normal
PROJECTION	1.1%	3.32 ms	1,000,000	26.70 MB	Projections: ['tx_id', 'amount', 'overall_median	Normal
PROJECTION	0.5%	1.59 ms	1,000,000	26.70 MB	Projections: ['tx_id', 'amount', 'overall_median	Normal
TABLE_SCAN	0.4%	1.23 ms	1,000,000	11.44 MB	memory.main.payment_volumes	Normal
CTE_SCAN	0.4%	1.22 ms	1,000,000	26.70 MB	-	Normal
CROSS_PRODUCT	0.3%	0.88 ms	1,000,000	34.33 MB	-	Normal
CROSS_PRODUCT	0.3%	0.74 ms	1,000,000	19.07 MB	-	Normal
TABLE_SCAN	0.2%	0.61 ms	1,000,000	7.63 MB	memory.main.payment_volumes	Normal
PROJECTION	0.0%	0.06 ms	1,000,000	7.63 MB	Projections: abs_deviation	Normal
PROJECTION	0.0%	0.06 ms	1,000,000	7.63 MB	Projections: amount	Normal
CTE_SCAN	0.0%	0.02 ms	1,000,000	26.70 MB	-	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH med AS
  (SELECT median(amount) AS overall_median
   FROM payment_volumes),
     deviations AS
  (SELECT p.tx_id,
          p.amount,
          m.overall_median,
          abs(p.amount - m.overall_median) AS abs_deviation
   FROM payment_volumes p
   CROSS JOIN med m),
     mad_val AS
  (SELECT median(abs_deviation) AS mad_score
   FROM deviations)
SELECT d.tx_id,
       d.amount,
       d.overall_median,
       round(d.abs_deviation / nullif(v.mad_score, 0), 2) AS robust_mad_score,
       CASE
           WHEN (d.abs_deviation / nullif(v.mad_score, 0)) > 3.0 THEN 'ROBUST_OUTLIER'
           ELSE 'NORMAL'
       END AS anomaly_flag
FROM deviations d
CROSS JOIN mad_val v
ORDER BY robust_mad_score DESC;

-- Compute Median Absolute Deviation (MAD) and flag robust outliers
 -- MAD = MEDIAN( |x - MEDIAN(x)| )
 WITH med AS
  (SELECT median(amount) AS overall_median
   FROM payment_volumes),
      deviations AS
  (SELECT p.tx_id,
          p.amount,
          m.overall_median,
          abs(p.amount - m.overall_median) AS abs_deviation
   FROM payment_volumes p
   CROSS JOIN med m),
      mad_val AS
  (SELECT median(abs_deviation) AS mad_score
   FROM deviations)
SELECT d.tx_id,
       d.amount,
       d.overall_median,
       round(d.abs_deviation / nullif(v.mad_score, 0), 2) AS robust_mad_score,
       CASE
           WHEN (d.abs_deviation / nullif(v.mad_score, 0)) > 3.0 THEN 'ROBUST_OUTLIER'
           ELSE 'NORMAL'
       END AS anomaly_flag
FROM deviations d
CROSS JOIN mad_val v
ORDER BY robust_mad_score DESC;

CREATE TABLE payment_volumes (tx_id INT, amount DOUBLE);


INSERT INTO payment_volumes
VALUES (1, 10.0),
       (2, 12.0),
       (3, 11.0),
       (4, 13.0),
       (5, 12.0),
       (6, 1000.0); -- Massive outlier

5. Query Results & Expected Ground Truth

Tabular Comparison

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

#	tx_id	amount	overall_median	robust_mad_score	anomaly_flag
1	218,474	2,064.58	177	10.66	ROBUST_OUTLIER
2	581,454	2,001.18	177	10.31	ROBUST_OUTLIER
3	188,896	1,991	177	10.25	ROBUST_OUTLIER
4	700,799	1,978.94	177	10.18	ROBUST_OUTLIER
5	896,122	1,973.87	177	10.15	ROBUST_OUTLIER
6	500,881	1,962.35	177	10.09	ROBUST_OUTLIER
7	764,403	1,959.39	177	10.07	ROBUST_OUTLIER
8	401,348	1,926.38	177	9.88	ROBUST_OUTLIER
9	20,909	1,918.01	177	9.84	ROBUST_OUTLIER
10	580,521	1,892.97	177	9.7	ROBUST_OUTLIER
11	562,740	1,883.84	177	9.64	ROBUST_OUTLIER
12	516,335	1,876.7	177	9.6	ROBUST_OUTLIER
13	582,594	1,856.91	177	9.49	ROBUST_OUTLIER
14	485,796	1,838.59	177	9.39	ROBUST_OUTLIER
15	828,286	1,836.94	177	9.38	ROBUST_OUTLIER
16	227,596	1,834.88	177	9.37	ROBUST_OUTLIER
17	697,776	1,828.31	177	9.33	ROBUST_OUTLIER
18	38,803	1,824.36	177	9.31	ROBUST_OUTLIER
19	456,005	1,822.75	177	9.3	ROBUST_OUTLIER
20	934,069	1,822.12	177	9.29	ROBUST_OUTLIER
21	966,674	1,820.77	177	9.29	ROBUST_OUTLIER
22	891,318	1,818.39	177	9.27	ROBUST_OUTLIER
23	206,231	1,815.03	177	9.25	ROBUST_OUTLIER
24	331,510	1,809.27	177	9.22	ROBUST_OUTLIER
25	446,409	1,808.2	177	9.22	ROBUST_OUTLIER

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

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

#	tx_id	amount	overall_median	robust_mad_score	anomaly_flag
1	218,474	2,064.58	177	10.66	ROBUST_OUTLIER
2	581,454	2,001.18	177	10.31	ROBUST_OUTLIER
3	188,896	1,991	177	10.25	ROBUST_OUTLIER
4	700,799	1,978.94	177	10.18	ROBUST_OUTLIER
5	896,122	1,973.87	177	10.15	ROBUST_OUTLIER
6	500,881	1,962.35	177	10.09	ROBUST_OUTLIER
7	764,403	1,959.39	177	10.07	ROBUST_OUTLIER
8	401,348	1,926.38	177	9.88	ROBUST_OUTLIER
9	20,909	1,918.01	177	9.84	ROBUST_OUTLIER
10	580,521	1,892.97	177	9.7	ROBUST_OUTLIER
11	562,740	1,883.84	177	9.64	ROBUST_OUTLIER
12	516,335	1,876.7	177	9.6	ROBUST_OUTLIER
13	582,594	1,856.91	177	9.49	ROBUST_OUTLIER
14	485,796	1,838.59	177	9.39	ROBUST_OUTLIER
15	828,286	1,836.94	177	9.38	ROBUST_OUTLIER
16	227,596	1,834.88	177	9.37	ROBUST_OUTLIER
17	697,776	1,828.31	177	9.33	ROBUST_OUTLIER
18	38,803	1,824.36	177	9.31	ROBUST_OUTLIER
19	456,005	1,822.75	177	9.3	ROBUST_OUTLIER
20	934,069	1,822.12	177	9.29	ROBUST_OUTLIER
21	966,674	1,820.77	177	9.29	ROBUST_OUTLIER
22	891,318	1,818.39	177	9.27	ROBUST_OUTLIER
23	206,231	1,815.03	177	9.25	ROBUST_OUTLIER
24	331,510	1,809.27	177	9.22	ROBUST_OUTLIER
25	446,409	1,808.2	177	9.22	ROBUST_OUTLIER

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

← Prev: List Lambdas for High-Per... ⊞ Back to Benchmark Dashboard Next: Robust Z-Score Normalizat... →