You have a Fabric workspace that contains a warehouse named DW1. DW1 contains the following tables and columns. Table name | Column name | Description ---|---|--- SalesOrderDetail | ProductID |…

This question tests knowledge of hierarchical aggregation in SQL using GROUP BY ROLLUP to produce both detail-level (year + product) and subtotal-level (year only) summaries of order quantities in Microsoft Fabric Warehouse.

Ingest and transform data

Question

You have a Fabric workspace that contains a warehouse named DW1. DW1 contains the following tables and columns.
Table nameColumn nameDescription
SalesOrderDetailProductIDContains the product ID of the ordered product
SalesOrderDetailModifiedDateContains the date of an order
SalesOrderDetailOrderQtyContains the order quantity
ProductProductIDContains the unique ID of a product
ProductNameContains a product name

You need to create an output that presents the summarized values of all the order quantities by year and product. The results must include a summary of the order quantities at the year level for all the products.
How should you complete the code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Table name	Column name	Description
SalesOrderDetail	ProductID	Contains the product ID of the ordered product
SalesOrderDetail	ModifiedDate	Contains the date of an order
SalesOrderDetail	OrderQty	Contains the order quantity
Product	ProductID	Contains the unique ID of a product
Product	Name	Contains a product name

Explanation

Approach. The correct approach is to JOIN SalesOrderDetail and Product on ProductID, aggregate SUM(OrderQty), and use GROUP BY ROLLUP(YEAR(ModifiedDate), p.Name). ROLLUP is the key operator here - it generates multiple grouping levels automatically: (Year, Product) for detail rows, (Year) for year-level subtotals with NULL for the product name, and () for a grand total. Without ROLLUP (or GROUPING SETS), a plain GROUP BY would only produce the Year+Product detail level and miss the required year-level rollup summary. A typical correct query looks like: SELECT YEAR(sod.ModifiedDate) AS OrderYear, p.Name AS ProductName, SUM(sod.OrderQty) AS TotalQty FROM SalesOrderDetail sod JOIN Product p ON sod.ProductID = p.ProductID GROUP BY ROLLUP(YEAR(sod.ModifiedDate), p.Name).

Concept tested. GROUP BY ROLLUP for hierarchical subtotals - understanding when and how to generate multi-level aggregations (detail + subtotal rows) in T-SQL within Microsoft Fabric Warehouse, as opposed to a flat GROUP BY which only produces one level of grouping.

Reference. Microsoft Learn - T-SQL GROUP BY ROLLUP (Transact-SQL): https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

Topics

#SQL Queries#Aggregation#Grouping Functions#Data Transformation

Community Discussion

No community discussion yet for this question.

Full DP-700 Practice