BenchMark Quizzes by MDFT Pro

Complete the T-SQL Query for Holiday Sales

You have a Fabric lakehouse named Lakehouse1 that contains the following data.

Table name	Column name	Data type
dbo.publicholidays	CountryOrReqion	Varchar(8000)
dbo.publicholidays	HolidayName	Varchar(8000)
dbo.publicholidays	Date	Date
dbo.sales	OrderDate	Date
dbo.sales	Quantity	Float
dbo.sales	UnitPrice	Float

You need build a T-SQL statement that will return the total sales amount by OrderDate only for the days that are holidays in Australia. The total sales amount must sum the quantity multiplied by the price on each row in the dbo.sales table.

You have the following T-SQL query:

SELECT s.OrderDate, 
______________ AS TotalSalesAmt
FROM [Lakehouse1].[dbo].[sales] AS s
INNER JOIN [Lakehouse1].[dbo].[publicholidays] AS ph ON s.OrderDate = ph.Date
WHERE ph.CountryOrRegion = 'Australia'
GROUP BY s.OrderDate

How should you complete the statement?

Choose the correct answer from the options below.

Explanations for each answer:

s.Quantity * s.UnitPrice is incorrect. This would calculate the sales amount for each individual row without summing them up. We need to aggregate the results by OrderDate using SUM.
SUM(s.Quantity * s.UnitPrice) is correct.
SUM(s.Quantity) * s.UnitPrice is incorrect. This would incorrectly multiply the sum of all quantities by a single unit price, rather than calculating the total sales amount for each order date.
s.Quantity * SUM(s.UnitPrice) is incorrect. This would incorrectly multiply a single quantity by the sum of all unit prices, rather than calculating the total sales amount for each order date.
SUM(s.Quantity) * SUM(s.UnitPrice) is incorrect. This would incorrectly multiply the sum of all quantities by the sum of all unit prices, resulting in a single total rather than totals by order date.

Learn more about the SUM aggregate function:

SUM Function

Exam Preparation Quiz

Complete the T-SQL Query for Holiday Sales