Employees With Deductions - Practice Coding Problems

Employees With Deductions - Problem

Database Medium

Imagine you're working as a payroll manager at a company where employees must meet specific monthly work hour requirements to receive their full salary. Your task is to identify which employees should face salary deductions due to insufficient work hours.

You have access to two key pieces of information:

Employee Requirements: Each employee has a minimum number of hours they must work monthly
Work Session Logs: Detailed timestamps of when employees clock in and out

Here's the twist: work sessions are rounded up to the nearest minute. So if an employee works 51 minutes and 2 seconds, it counts as 52 minutes. Sessions can even span midnight!

Your Goal: Return the employee_id of all employees who didn't meet their required work hours in October 2022.

Example: If Employee 1 needs 100 hours but only worked 95.5 hours total across all sessions, they should be flagged for deduction.

Input & Output

example_1.sql — Basic Case

$ Input: Employees table: +-------------+--------------+ | employee_id | needed_hours | +-------------+--------------+ | 1 | 20 | | 2 | 12 | | 3 | 2 | +-------------+--------------+ Logs table: +-------------+---------------------+---------------------+ | employee_id | in_time | out_time | +-------------+---------------------+---------------------+ | 1 | 2022-10-01 09:00:00 | 2022-10-01 17:00:00 | | 1 | 2022-10-02 09:00:00 | 2022-10-02 17:00:00 | | 2 | 2022-10-01 12:00:00 | 2022-10-01 17:00:00 | | 3 | 2022-10-01 07:00:00 | 2022-10-01 09:00:00 | +-------------+---------------------+---------------------+

› Output: +-------------+ | employee_id | +-------------+ | 2 | | 3 | +-------------+

💡 Note: Employee 1: worked 8+8=16 hours, needs 20 hours. Worked enough (16 >= 20 is false, so deducted). Employee 2: worked 5 hours, needs 12 hours. Not enough (5 < 12). Employee 3: worked 2 hours, needs 2 hours. Exactly enough (2 >= 2 is true, so not deducted). Wait, let me recalculate: Employee 1 worked 16 hours but needs 20, so gets deducted. Employee 2 worked 5 hours but needs 12, so gets deducted. Employee 3 worked 2 hours and needs 2, so no deduction.

example_2.sql — Rounding Up Minutes

$ Input: Employees table: +-------------+--------------+ | employee_id | needed_hours | +-------------+--------------+ | 1 | 1 | +-------------+--------------+ Logs table: +-------------+---------------------+---------------------+ | employee_id | in_time | out_time | +-------------+---------------------+---------------------+ | 1 | 2022-10-01 09:00:00 | 2022-10-01 09:59:30 | +-------------+---------------------+---------------------+

› Output: +-------------+ | employee_id | +-------------+ +-------------+

💡 Note: Employee 1 worked for 59 minutes and 30 seconds. Since we round up, this becomes 60 minutes = 1 hour. They needed 1 hour, so 1 >= 1 is true. No deduction needed.

example_3.sql — Employee With No Logs

$ Input: Employees table: +-------------+--------------+ | employee_id | needed_hours | +-------------+--------------+ | 1 | 8 | | 2 | 10 | +-------------+--------------+ Logs table: +-------------+---------------------+---------------------+ | employee_id | in_time | out_time | +-------------+---------------------+---------------------+ | 1 | 2022-10-01 09:00:00 | 2022-10-01 17:00:00 | +-------------+---------------------+---------------------+

› Output: +-------------+ | employee_id | +-------------+ | 2 | +-------------+

💡 Note: Employee 1 worked 8 hours and needs 8 hours, so no deduction. Employee 2 has no log entries, so worked 0 hours but needs 10 hours, resulting in deduction.

Constraints

1 ≤ employee_id ≤ 10⁴
1 ≤ needed_hours ≤ 50
1 ≤ Number of employees ≤ 500
1 ≤ Number of log entries ≤ 10⁴
All timestamps are in October 2022
out_time can be up to one day after in_time (overnight shifts)
Each session duration is at least 1 minute

Visualization

Tap to expand

Understanding the Visualization

Collect Time Cards

Gather all employee punch-in/punch-out records for the month

Calculate Session Times

For each session, calculate duration and round UP to nearest minute

Aggregate by Employee

Sum up all rounded session times for each employee

Compare with Quotas

Check each employee's total against their required monthly hours

Flag for Deduction

Identify employees who fell short of their requirements

Key Takeaway

🎯 Key Insight: Use SQL aggregation with proper rounding and LEFT JOIN to handle all edge cases (no logs, overnight shifts) in a single efficient query.

Asked in

a Amazon 35 ⊞ Microsoft 28 G Google 22 f Meta 18

The optimal solution uses SQL aggregation with GROUP BY to calculate total work hours for each employee in a single pass. Key insights: round up each session using CEIL(), handle employees with no logs using LEFT JOIN and COALESCE(), and leverage database optimization for O(n + m) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ One-Pass Aggregation (Optimal)	O(n + m)	O(n)	Use SQL GROUP BY to aggregate work hours and JOIN with requirements in single query
Brute Force (Nested Queries)	O(n × m)	O(1)	Calculate work hours for each employee separately using subqueries

One-Pass Aggregation (Optimal) — Algorithm Steps

Calculate total work minutes for each employee using SUM and GROUP BY
Use CEIL to round up each session duration before summing
Convert minutes to hours by dividing by 60
LEFT JOIN with Employees table to get required hours
Filter employees where actual < required hours
Handle employees with no logs using COALESCE

Visualization

Tap to expand

Step-by-Step Walkthrough

Initialize Hash Map

Create empty hash map to store work hours for each employee

Process Log Entries

For each log, calculate session time and add to employee's total

Round Up Sessions

Each session is rounded up to nearest minute before adding

Compare Requirements

Check each employee's total against their required hours

Collect Results

Add employees with insufficient hours to result list

Code -

solution.c — C

// Optimal: Simple array aggregation (assuming employee IDs are small)
#include <stdio.h>
#include <stdlib.h>
#include <math.h>
#include <string.h>

#define MAX_EMPLOYEES 1000

int* findEmployeesWithDeductions(Employee* employees, int emp_count,
                                LogEntry* logs, int log_count, int* result_size) {
    double workHours[MAX_EMPLOYEES] = {0};
    
    // Single pass: aggregate work hours
    for (int i = 0; i < log_count; i++) {
        int empId = logs[i].employee_id;
        double durationSeconds = difftime(logs[i].out_time, logs[i].in_time);
        double sessionMinutes = ceil(durationSeconds / 60.0);
        double sessionHours = sessionMinutes / 60.0;
        
        workHours[empId] += sessionHours;
    }
    
    // Find employees with insufficient hours
    int* result = malloc(emp_count * sizeof(int));
    *result_size = 0;
    
    for (int i = 0; i < emp_count; i++) {
        int empId = employees[i].employee_id;
        double actualHours = workHours[empId];
        
        if (actualHours < employees[i].needed_hours) {
            result[(*result_size)++] = empId;
        }
    }
    
    qsort(result, *result_size, sizeof(int), compare_ints);
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Where n is number of employees and m is number of log entries, processed once each

✓ Linear Growth

Space Complexity

O(n)

Space for grouping employees and storing intermediate results

⚡ Linearithmic Space

23.7K Views

Medium Frequency

~18 min Avg. Time

847 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

One-Pass Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler