Employees With Deductions

Employees With Deductions

What is this problem about?

The Employees With Deductions interview question involves calculating the total work time for each employee based on logs and identifying those who worked fewer hours than required. You are typically given a table of employees with their required work hours and a Logs table with in_time and out_time. The catch is that each work session's duration is often rounded up to the nearest minute or hour, depending on the specific rules of the problem.

Why is this asked in interviews?

Amazon and other large enterprises use this database interview pattern to test data aggregation and time-based arithmetic. It evaluates whether a candidate can handle timestamp calculations, rounding logic (using CEIL or ROUND), and complex grouping. It’s a practical scenario reflecting payroll or attendance tracking systems.

Algorithmic pattern used

The problem uses Timestamp Aggregation and Conditional Filtering.

1. Calculate the duration of each log entry: TIMESTAMPDIFF(SECOND, in_time, out_time).
2. Apply the rounding rule: Convert seconds to minutes and use CEIL() if the rule says "round up every session."
3. SUM the durations per employee using GROUP BY.
4. Join with the Employee table to compare the total worked time with the needed_hours.
5. Return employees where total_time < needed_hours.

Example explanation

Employee 1 needs 10 hours. Logs:

• Session 1: 59 minutes and 10 seconds. (Rounded up to 60 minutes).
• Session 2: 8 hours and 30 minutes. (Rounded up to 511 minutes? No, usually sessions are rounded individually). The sum of these rounded values must meet the hourly requirement.

Common mistakes candidates make

• Rounding at the end: Rounding the total sum instead of rounding each individual session, which can lead to different results.
• Time unit confusion: Mixing up seconds, minutes, and hours during the comparison.
• Missing employees: Not accounting for employees who have zero logs (they should definitely be flagged if they need hours).

Interview preparation tip

Familiarize yourself with SQL time functions like TIMESTAMPDIFF, DATE_ADD, and EXTRACT. Different databases (MySQL, PostgreSQL, Oracle) have different syntax for these, so specify which one you are using.