SQL-FRONTIER Benchmark Deep-Dive / Warehouse & Statistical / task_093_Q93_list_lambdas_nested_json_arrays
← Prev: Dynamic UNPIVOT of Wide T... ⊞ All 120 Queries Next: Median Absolute Deviation... →
✔ PASS Warehouse & Statistical task_093_Q93_list_lambdas_nested_json_arrays Scaled: 1M+ Records Ordered: Yes

List Lambdas for High-Performance Nested Array Transformations

Candidate Latency (1M Rows)
1554.30 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
1607.67 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: 13.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

Modern analytical warehouses store nested array payloads (e.g. array of discount percentages). Transforming or filtering elements directly inside the array without unnesting and re-aggregating saves massive compute.

⚡ The Frontier Trap (Why LLMs Fail)

Unnesting arrays to flat rows and re-aggregating with LIST() triggers expensive hash shuffles.

Prompt / User Request Given to Model
Modern analytical warehouses store nested array payloads (e.g. array of discount percentages). Transforming or filtering elements directly inside the array without unnesting and re-aggregating saves massive compute.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: cart_id, raw_discounts, qualifying_discounts, doubled_qualifying_discounts.
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:

📊 shopping_carts 1,000,000 rows 15.3 MiB Data Volume
cart_id (INTEGER) discounts_applied (INTEGER[]) item_prices (DOUBLE[])
Data Preview (showing sample rows from 1,000,000 total rows):
# cart_id discounts_applied item_prices
1 1 [ 5 15 25] [100. 50. 20.]
2 2 [ 0 10] [200. 80.]
3 2 [1 2 3] [0.8427 0.0896]
4 3 [2 3 4] [0.8573 0.0889]
5 4 [3 4 5] [0.9308 0.0987]
6 5 [4 5 6] [0.7976 0.1281]
7 6 [5 6 7] [0.9138 0.0412]
8 7 [1 7 8] [0.8427 0.1173]
9 8 [2 8 9] [0.844 0.1208]
10 9 [ 3 9 10] [0.8698 0.1105]
11 10 [ 4 10 11] [0.8209 0.0839]
12 11 [ 5 11 12] [0.8652 0.0448]
13 12 [ 1 2 13] [0.8155 0.1153]
14 13 [ 2 3 14] [0.8007 0.0808]
15 14 [ 3 4 15] [0.8369 0.0697]

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: 200.28 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 🔥 77.3% Cost
Latency: 154.80 ms
Rows In/Out: 1,000,000
Data Vol: 71.72 MB
⚠️ Heavy Execution Bottleneck (77.3% of query runtime)
📐 PROJECTION 6.3%
Latency: 12.68 ms
Rows In/Out: 1,000,000
Data Vol: 71.72 MB
📁 Projections: ['cart_id', 'raw_di...
📐 PROJECTION 13.0%
Latency: 26.02 ms
Rows In/Out: 1,000,000
Data Vol: 51.12 MB
📁 Projections: ['cart_id', 'raw_di...
🔍 TABLE_SCAN 3.4%
Latency: 6.78 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
📁 memory.main.shopping_carts
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
ORDER_BY 77.3% 154.80 ms 1,000,000 71.72 MB - 🔥 BOTTLENECK
PROJECTION 13.0% 26.02 ms 1,000,000 51.12 MB Projections: ['cart_id', 'raw_discounts', 'quali Normal
PROJECTION 6.3% 12.68 ms 1,000,000 71.72 MB Projections: ['cart_id', 'raw_discounts', 'quali Normal
TABLE_SCAN 3.4% 6.78 ms 1,000,000 30.52 MB memory.main.shopping_carts 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: 1554.30 ms) 
-- Filter out discounts <= 5% and double remaining discount percentages using list lambdas

SELECT cart_id,
       discounts_applied AS raw_discounts,
       list_filter(discounts_applied, lambda x: x > 5) AS qualifying_discounts,
       list_transform(list_filter(discounts_applied, lambda x: x > 5), lambda x: x * 2) AS doubled_qualifying_discounts
FROM shopping_carts
ORDER BY cart_id;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 1607.67 ms) 
-- Filter out discounts <= 5% and double remaining discount percentages using list lambdas

SELECT cart_id,
       discounts_applied AS raw_discounts,
       list_filter(discounts_applied, lambda x: x > 5) AS qualifying_discounts,
       list_transform(list_filter(discounts_applied, lambda x: x > 5), lambda x: x * 2) AS doubled_qualifying_discounts
FROM shopping_carts
ORDER BY cart_id;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE shopping_carts (cart_id INT, discounts_applied INT[], item_prices DOUBLE[]);


INSERT INTO shopping_carts
VALUES (1, [5, 15, 25], [100.0, 50.0, 20.0]),
       (2, [0, 10], [200.0, 80.0]);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (1,000,000 rows × 4 cols)
# cart_id raw_discounts qualifying_discounts doubled_qualifying_discounts
1 1 [ 5 15 25] [15 25] [30 50]
2 2 [ 0 10] [10] [20]
3 2 [1 2 3] [] []
4 3 [2 3 4] [] []
5 4 [3 4 5] [] []
6 5 [4 5 6] [6] [12]
7 6 [5 6 7] [6 7] [12 14]
8 7 [1 7 8] [7 8] [14 16]
9 8 [2 8 9] [8 9] [16 18]
10 9 [ 3 9 10] [ 9 10] [18 20]
11 10 [ 4 10 11] [10 11] [20 22]
12 11 [ 5 11 12] [11 12] [22 24]
13 12 [ 1 2 13] [13] [26]
14 13 [ 2 3 14] [14] [28]
15 14 [ 3 4 15] [15] [30]
16 15 [ 4 5 16] [16] [32]
17 16 [ 5 6 17] [ 6 17] [12 34]
18 17 [1 7 3] [7] [14]
19 18 [2 8 4] [8] [16]
20 19 [3 9 5] [9] [18]
21 20 [ 4 10 6] [10 6] [20 12]
22 21 [ 5 11 7] [11 7] [22 14]
23 22 [1 2 8] [8] [16]
24 23 [2 3 9] [9] [18]
25 24 [ 3 4 10] [10] [20]
Showing first 25 of 1,000,000 total rows.
Expected Golden Output (1,000,000 rows × 4 cols)
# cart_id raw_discounts qualifying_discounts doubled_qualifying_discounts
1 1 [ 5 15 25] [15 25] [30 50]
2 2 [1 2 3] [] []
3 2 [ 0 10] [10] [20]
4 3 [2 3 4] [] []
5 4 [3 4 5] [] []
6 5 [4 5 6] [6] [12]
7 6 [5 6 7] [6 7] [12 14]
8 7 [1 7 8] [7 8] [14 16]
9 8 [2 8 9] [8 9] [16 18]
10 9 [ 3 9 10] [ 9 10] [18 20]
11 10 [ 4 10 11] [10 11] [20 22]
12 11 [ 5 11 12] [11 12] [22 24]
13 12 [ 1 2 13] [13] [26]
14 13 [ 2 3 14] [14] [28]
15 14 [ 3 4 15] [15] [30]
16 15 [ 4 5 16] [16] [32]
17 16 [ 5 6 17] [ 6 17] [12 34]
18 17 [1 7 3] [7] [14]
19 18 [2 8 4] [8] [16]
20 19 [3 9 5] [9] [18]
21 20 [ 4 10 6] [10 6] [20 12]
22 21 [ 5 11 7] [11 7] [22 14]
23 22 [1 2 8] [8] [16]
24 23 [2 3 9] [9] [18]
25 24 [ 3 4 10] [10] [20]
Showing first 25 of 1,000,000 total rows.
← Prev: Dynamic UNPIVOT of Wide T... ⊞ Back to Benchmark Dashboard Next: Median Absolute Deviation... →