SQL-FRONTIER Benchmark Deep-Dive / Warehouse & Statistical / task_098_Q98_window_exclude_group_moving_peer
← Prev: SARGable Partition Prunin... ⊞ All 120 Queries Next: Window Frame Ties Exclusi... →
✔ PASS Warehouse & Statistical task_098_Q98_window_exclude_group_moving_peer Scaled: 1M+ Records Ordered: Yes

Window Peer Frame Exclusion: EXCLUDE GROUP for Clean Cross-Group Contrast

Candidate Latency (1M Rows)
304.29 ms
(1.1x slower than golden)
Golden Latency (1M Rows)
287.16 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
30.5 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 13.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

When comparing sales across product categories or dates, analysts want to compute the average of all other groups, strictly excluding the entire current group.

⚡ The Frontier Trap (Why LLMs Fail)

Computing group baselines without excluding the current group dilutes contrast in small sample sizes.

Prompt / User Request Given to Model
When comparing sales across product categories or dates, analysts want to compute the average of all other groups, strictly excluding the entire current group.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: region, order_id, sale_amount, other_regions_baseline_avg.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 regional_orders 1,000,000 rows 30.5 MiB Data Volume
region (VARCHAR) order_id (INTEGER) sale_amount (DOUBLE)
Data Preview (showing sample rows from 1,000,000 total rows):
# region order_id sale_amount
1 North 1 100
2 North 2 120
3 South 3 200
4 East 4 150
5 North 4 181.72
6 South 5 200.93
7 East 6 161.62
8 North 7 151.65
9 South 8 45.19
10 East 9 118.75
11 North 10 118.73
12 South 11 106.58
13 East 12 114.69
14 North 13 121.2
15 South 14 182.67

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: 598.57 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
📐 PROJECTION 0.7%
Latency: 4.24 ms
Rows In/Out: 1,000,000
Data Vol: 34.33 MB
📁 Projections: ['__internal_decomp...
⏳ ORDER_BY 🔥 15.5% Cost
Latency: 92.81 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
⚠️ Heavy Execution Bottleneck (15.5% of query runtime)
📐 PROJECTION 0.9%
Latency: 5.30 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
📁 Projections: ['__internal_compre...
📐 PROJECTION 4.4%
Latency: 26.56 ms
Rows In/Out: 1,000,000
Data Vol: 34.33 MB
📁 Projections: ['region', 'order_i...
📐 PROJECTION 0.0%
Latency: 0.18 ms
Rows In/Out: 1,000,000
Data Vol: 34.33 MB
📁 Projections: ['#0', '#1', '#2', ...
🪟 WINDOW 🔥 76.4% Cost
Latency: 457.17 ms
Rows In/Out: 1,000,000
Data Vol: 34.33 MB
📁 Projections: avg(sale_amount) OV...
⚠️ Heavy Execution Bottleneck (76.4% of query runtime)
🔍 TABLE_SCAN 2.1%
Latency: 12.30 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
📁 memory.main.regional_orders
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
WINDOW 76.4% 457.17 ms 1,000,000 34.33 MB Projections: avg(sale_amount) OVER (ORDER BY reg 🔥 BOTTLENECK
ORDER_BY 15.5% 92.81 ms 1,000,000 26.70 MB - 🔥 BOTTLENECK
PROJECTION 4.4% 26.56 ms 1,000,000 34.33 MB Projections: ['region', 'order_id', 'sale_amount Normal
TABLE_SCAN 2.1% 12.30 ms 1,000,000 26.70 MB memory.main.regional_orders Normal
PROJECTION 0.9% 5.30 ms 1,000,000 26.70 MB Projections: ['__internal_compress_string_ubigin Normal
PROJECTION 0.7% 4.24 ms 1,000,000 34.33 MB Projections: ['__internal_decompress_string(#0)' Normal
PROJECTION 0.0% 0.18 ms 1,000,000 34.33 MB Projections: ['#0', '#1', '#2', '#3'] 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: 304.29 ms) 
-- Compute baseline average across other regions strictly excluding current region group

SELECT region,
       order_id,
       sale_amount,
       round(avg(sale_amount) OVER (
                                    ORDER BY region ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING exclude GROUP), 2) AS other_regions_baseline_avg
FROM regional_orders
ORDER BY region,
         order_id;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 287.16 ms) 
-- Compute baseline average across other regions strictly excluding current region group

SELECT region,
       order_id,
       sale_amount,
       round(avg(sale_amount) OVER (
                                    ORDER BY region ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING exclude GROUP), 2) AS other_regions_baseline_avg
FROM regional_orders
ORDER BY region,
         order_id;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE regional_orders (region VARCHAR, order_id INT, sale_amount DOUBLE);


INSERT INTO regional_orders
VALUES ('North', 1, 100.0),
       ('North', 2, 120.0),
       ('South', 3, 200.0),
       ('East', 4, 150.0);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (1,000,000 rows × 4 cols)
# region order_id sale_amount other_regions_baseline_avg
1 East 4 150 142.6
2 East 6 161.62 142.6
3 East 9 118.75 142.6
4 East 12 114.69 142.6
5 East 15 148.65 142.6
6 East 18 79.06 142.6
7 East 21 117.49 142.6
8 East 24 158.04 142.6
9 East 27 177.98 142.6
10 East 30 82.82 142.6
11 East 33 146.35 142.6
12 East 36 165.93 142.6
13 East 39 176.27 142.6
14 East 42 172.07 142.6
15 East 45 83.76 142.6
16 East 48 220.74 142.6
17 East 51 140.26 142.6
18 East 54 126.64 142.6
19 East 57 78.23 142.6
20 East 60 131.33 142.6
21 East 63 112.56 142.6
22 East 66 142.36 142.6
23 East 69 166.73 142.6
24 East 72 216.69 142.6
25 East 75 189.09 142.6
Showing first 25 of 1,000,000 total rows.
Expected Golden Output (1,000,000 rows × 4 cols)
# region order_id sale_amount other_regions_baseline_avg
1 East 4 150 142.6
2 East 6 161.62 142.6
3 East 9 118.75 142.6
4 East 12 114.69 142.6
5 East 15 148.65 142.6
6 East 18 79.06 142.6
7 East 21 117.49 142.6
8 East 24 158.04 142.6
9 East 27 177.98 142.6
10 East 30 82.82 142.6
11 East 33 146.35 142.6
12 East 36 165.93 142.6
13 East 39 176.27 142.6
14 East 42 172.07 142.6
15 East 45 83.76 142.6
16 East 48 220.74 142.6
17 East 51 140.26 142.6
18 East 54 126.64 142.6
19 East 57 78.23 142.6
20 East 60 131.33 142.6
21 East 63 112.56 142.6
22 East 66 142.36 142.6
23 East 69 166.73 142.6
24 East 72 216.69 142.6
25 East 75 189.09 142.6
Showing first 25 of 1,000,000 total rows.
← Prev: SARGable Partition Prunin... ⊞ Back to Benchmark Dashboard Next: Window Frame Ties Exclusi... →