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

Dynamic PIVOT of Monthly Revenue by Product Category

Candidate Latency (1M Rows)

19.66 ms

(1.4x faster than golden)

Golden Latency (1M Rows)

27.08 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

53.4 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 4.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

Financial reporting requires transforming normalized long-form transaction rows into cross-tabulated wide financial matrices (Months along the top, Categories down the side).

⚡ The Frontier Trap (Why LLMs Fail)

Writing dozens of manual `CASE WHEN month = '2026-01' THEN amount ELSE 0 END` expressions is brittle and verbose.

Prompt / User Request Given to Model

Financial reporting requires transforming normalized long-form transaction rows into cross-tabulated wide financial matrices (Months along the top, Categories down the side).

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: category, 2026-01, 2026-02.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 sales_facts 1,000,000 rows 53.4 MiB Data Volume

category (VARCHAR) sale_month (VARCHAR) revenue (DECIMAL(10,2))

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

#	category	sale_month	revenue
1	Electronics	2026-01	50,000
2	Electronics	2026-02	65,000
3	Apparel	2026-01	30,000
4	Apparel	2026-02	32,000
5	Electronics	2026-01	63,648.95
6	Apparel	2026-02	48,396.95
7	Electronics	2026-01	68,336.32
8	Apparel	2026-02	39,315.96
9	Electronics	2026-01	41,847.9
10	Apparel	2026-02	47,419.78
11	Electronics	2026-01	59,108.65
12	Apparel	2026-02	58,100.22
13	Electronics	2026-01	27,536.96
14	Apparel	2026-02	66,193.31
15	Electronics	2026-01	58,730.51

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

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 96 B

📁 Projections: ['__internal_decomp...

⏳ ORDER_BY 0.1%

Latency: 0.07 ms

Rows In/Out: 2

Data Vol: 96 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 96 B

📁 Projections: ['__internal_compre...

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 96 B

📁 Projections: ['__internal_decomp...

📊 HASH_GROUP_BY 🔥 22.9% Cost

Latency: 16.52 ms

Rows In/Out: 2

Data Vol: 96 B

📐 PROJECTION 🔥 31.3% Cost

Latency: 22.56 ms

Rows In/Out: 1,000,000

Data Vol: 32.42 MB

📁 Projections: ['category', 'reven...

📐 PROJECTION 12.4%

Latency: 8.97 ms

Rows In/Out: 1,000,000

Data Vol: 38.15 MB

📁 Projections: ['__internal_compre...

🔍 TABLE_SCAN 🔥 33.3% Cost

Latency: 24.00 ms

Rows In/Out: 1,000,000

Data Vol: 38.15 MB

📁 memory.main.sales_facts

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
TABLE_SCAN	33.3%	24.00 ms	1,000,000	38.15 MB	memory.main.sales_facts	🔥 BOTTLENECK
PROJECTION	31.3%	22.56 ms	1,000,000	32.42 MB	Projections: ['category', 'revenue', "(sale_mont	🔥 BOTTLENECK
HASH_GROUP_BY	22.9%	16.52 ms	2	0.00 MB	-	🔥 BOTTLENECK
PROJECTION	12.4%	8.97 ms	1,000,000	38.15 MB	Projections: ['__internal_compress_string_uhugei	Normal
ORDER_BY	0.1%	0.07 ms	2	0.00 MB	-	Normal
PROJECTION	0.0%	0.00 ms	2	0.00 MB	Projections: ['__internal_decompress_string(#0)'	Normal
PROJECTION	0.0%	0.00 ms	2	0.00 MB	Projections: ['__internal_decompress_string(#0)'	Normal
PROJECTION	0.0%	0.00 ms	2	0.00 MB	Projections: ['__internal_compress_string_uhugei	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

PIVOT sales_facts ON sale_month IN ('2026-01',
                                    '2026-02') USING sum(revenue)
GROUP BY category
ORDER BY category;

-- Reshape monthly revenue into wide cross-tabulation using DuckDB PIVOT
 PIVOT sales_facts ON sale_month USING sum(revenue)
GROUP BY category
ORDER BY category;

CREATE TABLE sales_facts (category VARCHAR, sale_month VARCHAR, revenue decimal(10, 2));


INSERT INTO sales_facts
VALUES ('Electronics', '2026-01', 50000.00),
       ('Electronics', '2026-02', 65000.00),
       ('Apparel', '2026-01', 30000.00),
       ('Apparel', '2026-02', 32000.00);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (2 rows × 3 cols)

#	category	2026-01	2026-02
1	Apparel	30,000	22,133,130,154.47
2	Electronics	22,134,550,581.83	65,000

Expected Golden Output (2 rows × 3 cols)

#	category	2026-01	2026-02
1	Apparel	30,000	22,133,130,154.47
2	Electronics	22,134,550,581.83	65,000

← Prev: Three-Valued Logic Safe A... ⊞ Back to Benchmark Dashboard Next: Dynamic UNPIVOT of Wide T... →