Answer-first summary for fast verification
Answer: SELECT SUM(Sales.Amount) AS TotalSales, Products.Name AS ProductName, Customers.Name AS CustomerName FROM Sales JOIN Products ON Sales.ProductID = Products.ID JOIN Customers ON Sales.CustomerID = Customers.ID WHERE Sales.Date >= DATEADD(quarter, -1, GETDATE()) GROUP BY Products.Name, Customers.Name;
Option A correctly sums the sales amounts, joins the necessary tables, and filters the transactions to include only those from the last quarter. Options B, C, and D either do not sum the sales amounts correctly, do not filter the date correctly, or do not group by the necessary fields.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
Consider a scenario where you are tasked with querying a lakehouse in Fabric to extract sales data for the last quarter. The lakehouse contains multiple tables, including 'Sales', 'Products', and 'Customers'. Write a SQL query that joins these tables to retrieve the total sales amount, product name, and customer name for each transaction. Additionally, ensure that the query filters out any transactions that occurred before the last quarter. How would you structure this query?
A
SELECT SUM(Sales.Amount) AS TotalSales, Products.Name AS ProductName, Customers.Name AS CustomerName FROM Sales JOIN Products ON Sales.ProductID = Products.ID JOIN Customers ON Sales.CustomerID = Customers.ID WHERE Sales.Date >= DATEADD(quarter, -1, GETDATE()) GROUP BY Products.Name, Customers.Name;
B
SELECT Sales.Amount AS TotalSales, Products.Name AS ProductName, Customers.Name AS CustomerName FROM Sales JOIN Products ON Sales.ProductID = Products.ID JOIN Customers ON Sales.CustomerID = Customers.ID WHERE Sales.Date >= DATEADD(quarter, -1, GETDATE());
C
SELECT SUM(Sales.Amount) AS TotalSales, Products.Name AS ProductName, Customers.Name AS CustomerName FROM Sales JOIN Products ON Sales.ProductID = Products.ID JOIN Customers ON Sales.CustomerID = Customers.ID GROUP BY Products.Name, Customers.Name;
D
SELECT Sales.Amount AS TotalSales, Products.Name AS ProductName, Customers.Name AS CustomerName FROM Sales JOIN Products ON Sales.ProductID = Products.ID JOIN Customers ON Sales.CustomerID = Customers.ID;
No comments yet.