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: ORDER 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 SalesOrderID)

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