Answer-first summary for fast verification
Answer: SELECT CategoryName, COUNT(*) AS ProductCount FROM ProductCategories GROUP BY CategoryName HAVING COUNT(*) >= 10;
Option A is the correct choice because it correctly uses the GROUP BY clause to aggregate the data by category name, the COUNT(*) function to calculate the number of products in each category, and the HAVING clause to filter out categories with fewer than 10 products. This approach is optimized for performance and adheres to SQL best practices for querying large datasets in a lakehouse environment. Option B incorrectly uses the WHERE clause with an aggregate function, which is not allowed in SQL. Option C incorrectly uses SUM(ProductCount) instead of COUNT(*), and Option D incorrectly places the WHERE clause after GROUP BY, which is syntactically incorrect.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
As a Fabric Analytics Engineer, you are working on a project that requires analyzing product data stored in a Fabric lakehouse. The dataset includes product categories with attributes such as category ID, category name, and the number of products in each category. Your task is to write a SQL query that retrieves the category name and the number of products for each category, but only for categories that have 10 or more products. Additionally, the solution must be optimized for performance and adhere to best practices for querying large datasets in a lakehouse environment. Choose the best SQL query from the options below to accomplish this task. (Select one option)
A
SELECT CategoryName, COUNT() AS ProductCount FROM ProductCategories GROUP BY CategoryName HAVING COUNT() >= 10;
B
SELECT CategoryName, COUNT(*) AS ProductCount FROM ProductCategories WHERE ProductCount >= 10 GROUP BY CategoryName;
C
SELECT CategoryName, SUM(ProductCount) AS TotalProductCount FROM ProductCategories GROUP BY CategoryName HAVING TotalProductCount >= 10;
D
SELECT CategoryName, COUNT(*) AS ProductCount FROM ProductCategories GROUP BY CategoryName WHERE ProductCount >= 10;
No comments yet.