Manipulating records in a dynamically computed range value

In the following example:

• Use GROUP to calculate a range of interest.
• Use an empty lookup list to get the range of interest into the desired expression.
• Use subtraction and HAVING to enable filtering by a dynamic value (HAVING must be used because Diff is not in scope in a WHERE clause on Result).

DEFINE CustomerTotals AS SELECT
    SUM(SalesAmount) AS Total
GROUP BY CustomerKey ;

DEFINE Range AS SELECT
    MAX(Total) AS MaxVal,
    MIN(Total) AS MinVal,
    ((MaxVal-MinVal)/10) AS Decile,
    MinVal + (Decile*9) AS Top10Pct
FROM CustomerTotals GROUP ;

RETURN Result AS SELECT
    SUM(SalesAmount) AS Total,
    Total-Range[].Top10Pct AS Diff
GROUP BY CustomerKey
HAVING Diff>0