SQL-FRONTIER Benchmark Deep-Dive / Quadratic Self-Joins vs Range Windows / task_07_quadratic_self_join
← Prev: Cycle Detection in Direct... ⊞ All 120 Queries Next: Lowest-Cost Routing acros... →
✔ PASS Quadratic Self-Joins vs Range Windows task_07_quadratic_self_join Scaled: 1M+ Records Ordered: Yes

Rolling 7-Day Window Aggregation without Quadratic Self-Joins

Candidate Latency (1M Rows)
2.79 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
2.81 ms
DuckDB in-memory engine
Underlying Data Volume
2,500 rows
48.8 KiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 4.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

Tests whether models leverage native SQL range window frames over non-equi self-joins that cause quadratic memory and CPU explosions.

⚡ The Frontier Trap (Why LLMs Fail)

Frontier models frequently generate triangular self-joins (JOIN ON b.date BETWEEN a.date - 6 AND a.date) instead of standard SQL RANGE window functions. On enterprise data warehouses (Snowflake, BigQuery, DuckDB), this produces an O(N^2) join operator (such as PIECEWISE_MERGE_JOIN or nested-loop joins) that degrades from linear to quadratic complexity and risks memory spills or warehouse timeouts.

Prompt / User Request Given to Model
Calculate the 7-day rolling total revenue for each date in `daily_revenue`.

The 7-day rolling revenue for a given date is the sum of `gross_amount` for that date and the preceding 6 calendar days (e.g., INTERVAL 6 DAYS).

Required output columns:
- revenue_date (DATE)
- gross_amount (DECIMAL)
- rolling_7d_revenue (DECIMAL)

Sort the results by revenue_date ASC.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (2,500 rows • 48.8 KiB)

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

📊 daily_revenue 2,500 rows 48.8 KiB Data Volume
revenue_date (DATE) gross_amount (DECIMAL(10,2))
Data Preview (showing sample rows from 2,500 total rows):
# revenue_date gross_amount
1 2025-01-01 00:00:00 100
2 2025-01-02 00:00:00 150
3 2025-01-03 00:00:00 200
4 2025-01-04 00:00:00 120
5 2025-01-05 00:00:00 180
6 2025-01-06 00:00:00 220
7 2025-01-07 00:00:00 300
8 2025-01-08 00:00:00 250
9 2026-01-01 00:00:00 93.01
10 2026-01-02 00:00:00 135.06
11 2026-01-03 00:00:00 155.92
12 2026-01-04 00:00:00 206.08
13 2026-01-05 00:00:00 206.6
14 2026-01-06 00:00:00 195.48
15 2026-01-07 00:00:00 156.87

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: 2.79 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 8.4%
Latency: 0.23 ms
Rows In/Out: 2,500
Data Vol: 0.07 MB
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 2,500
Data Vol: 0.07 MB
📁 Projections: ['#0', '#1', '#2']
🪟 WINDOW 🔥 90.7% Cost
Latency: 2.53 ms
Rows In/Out: 2,500
Data Vol: 0.07 MB
📁 Projections: sum_no_overflow(gro...
⚠️ Heavy Execution Bottleneck (90.7% of query runtime)
🔍 TABLE_SCAN 0.9%
Latency: 0.02 ms
Rows In/Out: 2,500
Data Vol: 0.03 MB
📁 memory.main.daily_revenue
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
WINDOW 90.7% 2.53 ms 2,500 0.07 MB Projections: sum_no_overflow(gross_amount) OVER 🔥 BOTTLENECK
ORDER_BY 8.4% 0.23 ms 2,500 0.07 MB - Normal
TABLE_SCAN 0.9% 0.02 ms 2,500 0.03 MB memory.main.daily_revenue Normal
PROJECTION 0.0% 0.00 ms 2,500 0.07 MB Projections: ['#0', '#1', '#2'] 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: 2.79 ms) 
SELECT revenue_date,
       gross_amount,
       sum(gross_amount) OVER (
                               ORDER BY revenue_date RANGE BETWEEN INTERVAL 6 days PRECEDING AND CURRENT ROW) AS rolling_7d_revenue
FROM daily_revenue
ORDER BY revenue_date ASC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 2.81 ms) 
SELECT revenue_date,
       gross_amount,
       sum(gross_amount) OVER (
                               ORDER BY revenue_date RANGE BETWEEN INTERVAL 6 days PRECEDING AND CURRENT ROW) AS rolling_7d_revenue
FROM daily_revenue
ORDER BY revenue_date ASC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE daily_revenue (revenue_date DATE, gross_amount decimal(10, 2));


INSERT INTO daily_revenue
VALUES ('2025-01-01', 100.00),
       ('2025-01-02', 150.00),
       ('2025-01-03', 200.00),
       ('2025-01-04', 120.00),
       ('2025-01-05', 180.00),
       ('2025-01-06', 220.00),
       ('2025-01-07', 300.00),
       ('2025-01-08', 250.00);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (2,500 rows × 3 cols)
# revenue_date gross_amount rolling_7d_revenue
1 2025-01-01 00:00:00 100 100
2 2025-01-02 00:00:00 150 250
3 2025-01-03 00:00:00 200 450
4 2025-01-04 00:00:00 120 570
5 2025-01-05 00:00:00 180 750
6 2025-01-06 00:00:00 220 970
7 2025-01-07 00:00:00 300 1,270
8 2025-01-08 00:00:00 250 1,420
9 2026-01-01 00:00:00 93.01 5,148.61
10 2026-01-01 00:00:00 338.92 5,148.61
11 2026-01-01 00:00:00 233.32 5,148.61
12 2026-01-01 00:00:00 58.05 5,148.61
13 2026-01-01 00:00:00 126.93 5,148.61
14 2026-01-01 00:00:00 101.72 5,148.61
15 2026-01-01 00:00:00 158.79 5,148.61
16 2026-01-01 00:00:00 228.22 5,148.61
17 2026-01-01 00:00:00 185.93 5,148.61
18 2026-01-01 00:00:00 239.48 5,148.61
19 2026-01-01 00:00:00 140.86 5,148.61
20 2026-01-01 00:00:00 167.71 5,148.61
21 2026-01-01 00:00:00 218.29 5,148.61
22 2026-01-01 00:00:00 338.55 5,148.61
23 2026-01-01 00:00:00 183.35 5,148.61
24 2026-01-01 00:00:00 150.6 5,148.61
25 2026-01-01 00:00:00 203.66 5,148.61
Showing first 25 of 2,500 total rows.
Expected Golden Output (2,500 rows × 3 cols)
# revenue_date gross_amount rolling_7d_revenue
1 2025-01-01 00:00:00 100 100
2 2025-01-02 00:00:00 150 250
3 2025-01-03 00:00:00 200 450
4 2025-01-04 00:00:00 120 570
5 2025-01-05 00:00:00 180 750
6 2025-01-06 00:00:00 220 970
7 2025-01-07 00:00:00 300 1,270
8 2025-01-08 00:00:00 250 1,420
9 2026-01-01 00:00:00 93.01 5,148.61
10 2026-01-01 00:00:00 338.92 5,148.61
11 2026-01-01 00:00:00 233.32 5,148.61
12 2026-01-01 00:00:00 58.05 5,148.61
13 2026-01-01 00:00:00 126.93 5,148.61
14 2026-01-01 00:00:00 101.72 5,148.61
15 2026-01-01 00:00:00 158.79 5,148.61
16 2026-01-01 00:00:00 228.22 5,148.61
17 2026-01-01 00:00:00 185.93 5,148.61
18 2026-01-01 00:00:00 239.48 5,148.61
19 2026-01-01 00:00:00 140.86 5,148.61
20 2026-01-01 00:00:00 167.71 5,148.61
21 2026-01-01 00:00:00 218.29 5,148.61
22 2026-01-01 00:00:00 338.55 5,148.61
23 2026-01-01 00:00:00 183.35 5,148.61
24 2026-01-01 00:00:00 150.6 5,148.61
25 2026-01-01 00:00:00 203.66 5,148.61
Showing first 25 of 2,500 total rows.
← Prev: Cycle Detection in Direct... ⊞ Back to Benchmark Dashboard Next: Lowest-Cost Routing acros... →