You have a Fabric warehouse that contains a table named SalesOrderDetail.

Comprehensive Detailed Explanation

We need to calculate the proportion of the total quantity of each sales order represented by each product within the sales order .

Step 1: Analyze the requirement

Table: SalesOrderDetail

Columns: OrderQty , ProductID , SalesOrderID

Each row = one product in a sales order.

Requirement: For each SalesOrderID , calculate what percentage each product contributes to the total order quantity.

This means we must:

Calculate the total order quantity per SalesOrderID .

Divide each product’s OrderQty by that total.

Express it as a percentage.

Step 2: Evaluate the options

A. Uses OVER(ORDER BY ProductID)

Wrong: OR DER BY does not segment data, it only defines sequence. We need grouping by sales order.

B. Uses OVER(PARTITION BY ProductID)

Wrong: This would calculate percentages per product across all sales orders, not per sales order.

C. Uses OVER(PARTITION BY SalesO rderID)

Correct: This computes the total OrderQty per SalesOrderID and divides each product’s quantity by that total. Exactly what the requirement asks.

D. Uses OVER(ORDER BY SalesOrderID)

Wrong: Again, ORDER BY just sequences rows, does not group them.

Step 3: Correct Query

SELECT

SalesOrderID,

ProductID,

OrderQty,

CAST ( 1. * OrderQty / SUM (OrderQty)

OVER ( PARTITION BY SalesOrderID) * 100 AS DECIMAL ( 5 , 2 ))

AS PercentByProductID

FROM Sales.SalesOrderDetail;

Why Option C is Correct

PARTITION BY SalesOrderID ensures the denominator is the total quantity for the current order .

This produces the correct percentage breakdown of each product inside its sales order.

References

T-SQL Window Functions

Aggregate Functions with PARTITION BY

Microsoft Fabric Warehouse T-SQL Support