Answer-first summary for fast verification
Answer: SELECT EmployeeID, AVG(PerformanceScore) AS AvgScore FROM EmployeeReviews GROUP BY EmployeeID HAVING COUNT(*) >= 3 ORDER BY AvgScore DESC;
The correct SQL query is option B. This query correctly calculates the average performance score for each employee using the AVG() function, filters out employees with fewer than 3 reviews using the HAVING clause, and orders the results by the average score in descending order to meet the report requirements. Option A orders the results in ascending order, which does not meet the requirement to sort from highest to lowest. Option C, while it calculates the average correctly, is less efficient than using the AVG() function. Option D incorrectly uses the MAX() function instead of calculating the average.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
As a Fabric Analytics Engineer, you are working with a lakehouse that contains a dataset of employee performance reviews. The dataset includes columns for EmployeeID, ReviewDate, and PerformanceScore. Your manager has asked you to generate a report that shows the average performance score for each employee, sorted from highest to lowest average score. Additionally, the report must exclude any employees who have fewer than 3 reviews to ensure statistical significance. Which SQL query would you use to accomplish this task? Choose the correct option from the following:
A
SELECT EmployeeID, AVG(PerformanceScore) AS AvgScore FROM EmployeeReviews GROUP BY EmployeeID HAVING COUNT(*) >= 3 ORDER BY AvgScore ASC;
B
SELECT EmployeeID, AVG(PerformanceScore) AS AvgScore FROM EmployeeReviews GROUP BY EmployeeID HAVING COUNT(*) >= 3 ORDER BY AvgScore DESC;
C
SELECT EmployeeID, SUM(PerformanceScore) / COUNT() AS AvgScore FROM EmployeeReviews GROUP BY EmployeeID HAVING COUNT() >= 3 ORDER BY AvgScore DESC;
D
SELECT EmployeeID, MAX(PerformanceScore) AS AvgScore FROM EmployeeReviews GROUP BY EmployeeID HAVING COUNT(*) >= 3 ORDER BY AvgScore DESC;
No comments yet.