Answer-first summary for fast verification
Answer: SELECT ProductName, SUM(UnitsSold) AS TotalUnitsSold FROM ProductSales GROUP BY ProductName HAVING SUM(UnitsSold) > 0;
Option B is the correct answer because it efficiently groups the data by product name, calculates the total units sold for each product using the SUM() function, and then filters out products with zero sales using the HAVING clause. This approach is more scalable for large datasets as it applies the filter after aggregation, reducing the amount of data processed early in the query execution. Options A and C filter before aggregation, which might not be as efficient for very large datasets. Option D uses an EXCEPT clause, which is less efficient and not the best practice for this scenario.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
You are working as a Fabric Analytics Engineer and are tasked with optimizing a SQL query for a dataset containing product sales information. The dataset includes columns for product ID, product name, and units sold. Your goal is to retrieve the product name and the total number of units sold for each product, excluding products with zero sales, while ensuring the query is efficient and scalable for large datasets. Which of the following SQL queries best meets these requirements? (Choose one option)
A
SELECT ProductName, SUM(UnitsSold) AS TotalUnitsSold FROM ProductSales WHERE UnitsSold > 0 GROUP BY ProductName;
B
SELECT ProductName, SUM(UnitsSold) AS TotalUnitsSold FROM ProductSales GROUP BY ProductName HAVING SUM(UnitsSold) > 0;
C
SELECT ProductName, SUM(UnitsSold) AS TotalUnitsSold FROM ProductSales WHERE UnitsSold <> 0 GROUP BY ProductName;
D
SELECT ProductName, SUM(UnitsSold) AS TotalUnitsSold FROM ProductSales EXCEPT SELECT ProductName FROM ProductSales WHERE UnitsSold = 0 GROUP BY ProductName;
No comments yet.