Calculated field
{ FIXED [Region] : MAX({ FIXED [Customer] : SUM([Revenue]) }) }
Sample data
| Region | Team | Customer | Revenue | Seats | Date |
|---|---|---|---|---|---|
| North | Cedar | Aster | 120 | 4 | 2026-01-05 |
| North | Cedar | Aster | 80 | 2 | 2026-02-05 |
| North | Cedar | Birch | 300 | 6 | 2026-01-12 |
| North | Pine | Clover | 90 | 3 | 2026-02-14 |
| South | Elm | Dahlia | 200 | 5 | 2026-01-08 |
| South | Elm | Dahlia | 100 | 2 | 2026-02-08 |
| South | Ash | Fern | 75 | 3 | 2026-01-20 |
| South | Ash | Fern | 25 | 1 | 2026-02-20 |
The same calculation as SQL
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MAX(total) FROM customers GROUP BY Region ORDER BY Region
Runs the example above.
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MAX(total) FROM customers GROUP BY Region ORDER BY Region
Written for this dialect; not run here.
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MAX(total) FROM customers GROUP BY Region ORDER BY Region
Written for this dialect; not run here.
Result
| Region | result |
|---|---|
| North | 300 |
| South | 300 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- MAX of individual invoices can miss the largest customer lifetime total.
Naive SQL
SELECT "Region", MAX(Revenue) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Region result North 300 South 200 Matches the calculation
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MAX(total) FROM customers GROUP BY Region ORDER BY Region
Region result North 300 South 300
Signature
{ FIXED [Region] : MAX({ FIXED [Customer] : SUM([Revenue]) }) }