BenchMark Quizzes by MDFT Pro

Complete SQL Query For Hierarchical Summaries

You work as a Business Intelligence Developer for Mark at MDFT Pro, a well-known training agency. MDFT Pro operates a Fabric workspace that contains a warehouse named DW1, which stores sales data for training courses and educational materials. The warehouse contains a SalesOrderDetail table tracking order quantities with product IDs and modification dates, and a Product table with product names. Mark has asked you to create a report that presents summarized values of all order quantities by year and product. The business requirement specifies that the results must include not only the detailed year-product combinations but also a summary of the order quantities at the year level for all products combined, allowing stakeholders to see both granular and aggregated sales patterns.

The warehouse contains the following structure:

Table Name	Column Name	Description
SalesOrderDetail	ProductID	The ID of the ordered product
SalesOrderDetail	ModifiedDate	The date of the order
SalesOrderDetail	OrderQty	The number of products ordered
Product	ProductID	The unique ID of a product
Product	Name	The name of a product

How should you complete the code?

SELECT
    YEAR(ModifiedDate) AS OrderDate,
    p.Name AS ProductName,
    SUM(so.OrderQty) AS OrderQty
FROM
    dbo.SalesOrderDetail so
    INNER JOIN dbo.Product p ON p.ProductID = so.ProductID
GROUP BY
    ____________________

Choose the correct answer from the options below.

Explanations for each answer:

CUBE (YEAR (so.ModifiedDate), p.Name) is correct. CUBE generates all possible combinations of groupings, including grand totals for years, products, year-product combinations, and an overall grand total. This satisfies the requirement to include a summary of order quantities at the year level for all products.
GROUPING SETS ((YEAR (so.ModifiedDate), p.Name), (YEAR (so.ModifiedDate))) is incorrect. While GROUPING SETS would provide year-product combinations and year-level summaries, it requires explicit specification of each grouping level and is more verbose than CUBE. It also doesn't automatically include all combinations that CUBE provides.
ROLLUP (YEAR (so.ModifiedDate), p.Name) is incorrect. ROLLUP creates a hierarchy from left to right, providing year-product combinations, year totals, and grand totals. However, it doesn't provide product-level summaries across all years, which CUBE does.
YEAR (so.ModifiedDate), p.Name is incorrect. A standard GROUP BY without aggregation modifiers only provides detailed results grouped by year and product name, without any summary rows at the year level or grand totals as required.

Learn more about SQL GROUP BY CUBE and ROLLUP:

SQL GROUP BY Operations

Practice Exam

Complete SQL Query For Hierarchical Summaries

Discuss this question on social media: