Microsoft Fabric Analytics Engineer Associate DP-600
Get started today
Ultimate access to all questions.
You manage a Fabric warehouse with a table named Staging.Sales. The Staging.Sales table includes the following columns: ProductID, ProductName, Amount, and SaleDate. Your task is to compose a T-SQL query that retrieves data for the year 2023, displaying the ProductID and ProductName for products that have a total Amount greater than 10,000. What query should you use?
Exam-Like
A
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000
B
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000 AND DATEPART(YEAR, SaleDate) = '2023'
C
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount WHERE DATEPART(YEAR, SaleDate) = '2023' AND SUM(Amount) > 10000 GROUP BY ProductID, ProductName FROM Staging.Sales
D
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE YEAR(SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING TotalAmount > 10000
Explanation:
The correct query should use a WHERE clause to filter the data for the year 2023, and then use the HAVING clause to filter groups based on the summarized Amount being greater than 10,000. Option A correctly uses these constructs. First, data is filtered for the year 2023 using DATEPART(YEAR, SaleDate) in the WHERE clause. Then, the data is grouped by ProductID and ProductName and the SUM(Amount) is calculated. Finally, the HAVING clause ensures only those groups with SUM(Amount) greater than 10,000 are returned. Using SUM(Amount) directly in the HAVING clause is correct since aggregate functions cannot be used with column aliases in the HAVING clause.
Comments
Loading comments...