Answer-first summary for fast verification
Answer: SELECT EmployeeID, SUM(HoursWorked) AS TotalHours, MAX(WorkDate) AS LastWorkDate FROM EmployeeWorkHours WHERE WorkDate BETWEEN '2022-03-01' AND '2022-03-31' GROUP BY EmployeeID;
Option D is the correct choice because it accurately filters the data for the specified month and year using the BETWEEN operator, which is more efficient and clearer in intent than using YEAR and MONTH functions. It groups the results by EmployeeID, calculates the total hours worked with SUM(HoursWorked), and identifies the last work date with MAX(WorkDate), meeting all the report requirements. This approach also optimizes performance by reducing the amount of data scanned during query execution.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
You are working as a Fabric Analytics Engineer and have been tasked with generating a report that summarizes the total hours worked by each employee in a specific month and year. The dataset includes columns for EmployeeID, WorkDate, and HoursWorked. The report must include the EmployeeID, the total hours worked, and the last date the employee worked during the specified month. Additionally, the solution must optimize for performance by minimizing the data scanned during the query execution. Which of the following SQL queries would you use to achieve this requirement? (Choose one option.)
A
SELECT EmployeeID, SUM(HoursWorked) AS TotalHours, MIN(WorkDate) AS FirstWorkDate FROM EmployeeWorkHours WHERE YEAR(WorkDate) = 2022 AND MONTH(WorkDate) = 3 GROUP BY EmployeeID;
B
SELECT EmployeeID, SUM(HoursWorked) AS TotalHours, MAX(WorkDate) AS LastWorkDate FROM EmployeeWorkHours WHERE YEAR(WorkDate) = 2022 AND MONTH(WorkDate) = 3 GROUP BY EmployeeID;
C
SELECT EmployeeID, SUM(HoursWorked) AS TotalHours, AVG(WorkDate) AS AvgWorkDate FROM EmployeeWorkHours WHERE YEAR(WorkDate) = 2022 AND MONTH(WorkDate) = 3 GROUP BY EmployeeID;
D
SELECT EmployeeID, SUM(HoursWorked) AS TotalHours, MAX(WorkDate) AS LastWorkDate FROM EmployeeWorkHours WHERE WorkDate BETWEEN '2022-03-01' AND '2022-03-31' GROUP BY EmployeeID;