Leetcode Problem 2394. Employees With Deductions

2394. Employees With Deductions

Leetcode Solutions

Calculating Work Hours and Identifying Employees with Insufficient Hours

Join the Employees table with the Logs table using a LEFT JOIN to include all employees, even those without any logs.
Group the results by employee_id to prepare for aggregation.
Use the TIMESTAMPDIFF function to calculate the difference in seconds between in_time and out_time for each log entry.
Apply the CEILING function to round up the time difference to the nearest minute.
Sum the rounded minutes for each employee to get the total minutes worked.
Convert the total minutes to hours by dividing by 60.
Use the IFNULL function to handle cases where there are no log entries for an employee, defaulting to 0.
Compare the total hours worked to the needed_hours for each employee.
Use the HAVING clause to filter out employees who have met or exceeded the required hours.
Select the employee_id of employees who have not worked the required hours.

erDiagram
    Employees {
        int employee_id
        int needed_hours
    }
    Logs {
        int employee_id
        datetime in_time
        datetime out_time
    }
    Employees ||--o{ Logs : "has"

Aggregating Worked Minutes and Comparing with Required Hours

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 2394. Employees With Deductions

Leetcode Solutions

Calculating Work Hours and Identifying Employees with Insufficient Hours

Solution Idea

Query Steps

Code Implementation

Aggregating Worked Minutes and Comparing with Required Hours

Ask Question

Suggested Answer