LeetCode Problem 3611

Difficulty: 🟡 Medium
Topics: Database

Solution

LeetCode 3611 - Find Overbooked Employees

Problem Understanding

This problem asks us to identify employees who spend an excessive amount of their working time in meetings.

We are given two tables:

The employees table contains employee information, including their unique identifier, name, and department.

The meetings table contains meeting attendance records. Each record tells us which employee attended a meeting, on what date it occurred, what type of meeting it was, and how many hours it lasted.

The company defines a standard work week as 40 hours. An employee is considered meeting-heavy during a particular week if they spend more than 20 hours in meetings during that week, because 20 hours is 50% of a 40 hour work week.

The weeks must be calculated using a Monday-to-Sunday calendar. Therefore, all meeting records that fall within the same Monday-Sunday period belong to the same week.

The task consists of several aggregation stages:

1. Group meetings by employee and week.
2. Sum the meeting durations for each employee-week combination.
3. Determine whether that weekly total exceeds 20 hours.
4. Count how many meeting-heavy weeks each employee has.
5. Keep only employees with at least 2 meeting-heavy weeks.
6. Return employee information together with the number of meeting-heavy weeks.
7. Sort by meeting-heavy weeks descending, then employee name ascending.

The important observation is that the result is not based on total meeting hours across all time. Instead, the threshold must be evaluated independently for every week. An employee with 40 meeting hours spread across multiple weeks might not qualify if no individual week exceeds 20 hours.

Important Edge Cases

An employee may have no meetings at all. Such employees should never appear in the result because they cannot have any meeting-heavy weeks.

An employee may have multiple meetings in the same week. All of those durations must be summed before comparing against the 20-hour threshold.

An employee may have exactly 20 hours of meetings during a week. The problem explicitly requires meeting hours to be greater than 20, not greater than or equal to 20.

An employee may have many qualifying weeks. All qualifying weeks must be counted before applying the requirement of at least two meeting-heavy weeks.

Approaches

Brute Force Approach

A straightforward approach would be to process every employee independently.

For each employee, we could scan the entire meetings table and collect all meetings belonging to that employee. Then we could determine the week for each meeting, accumulate weekly totals, identify qualifying weeks, and finally count how many qualifying weeks exist.

This approach is correct because every employee's weekly meeting totals are computed exactly as defined. However, it repeatedly scans the meetings table for every employee, causing unnecessary work.

If there are E employees and M meeting records, the meetings table could be scanned up to E times, resulting in O(E × M) work.

Optimal Approach

The key insight is that meeting-heavy status depends only on the combination of:

• Employee
• Week

Therefore, instead of processing employees separately, we should aggregate the meetings table once.

We first group records by (employee_id, week) and compute total meeting hours for each employee-week pair.

After obtaining weekly totals, we filter those whose total exceeds 20 hours. Each remaining row represents one meeting-heavy week.

Finally, we count how many such weeks belong to each employee and keep only employees whose count is at least 2.

This approach performs the aggregation directly at the database level and avoids repeatedly scanning the same meeting records.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(E × M)	O(M)	Repeatedly scans meetings for every employee
Optimal	O(M)	O(M)	Aggregate employee-week totals once

Algorithm Walkthrough

Optimal SQL Algorithm

1. Compute the start of the week for every meeting record using a Monday-Sunday calendar.
2. Group records by (employee_id, week_start) and calculate the sum of duration_hours for each group. This gives total meeting hours for every employee during every week.
3. Filter the grouped results to keep only weeks where total meeting hours exceed 20.
4. Group the remaining records by employee and count how many meeting-heavy weeks each employee has.
5. Keep only employees whose count is at least 2.
6. Join the aggregated results with the employees table so that employee name and department can be included.
7. Sort the final output by:

• meeting_heavy_weeks descending
• employee_name ascending

Why it works

The algorithm works because every meeting belongs to exactly one Monday-Sunday week. Summing meeting durations within each employee-week group produces the exact weekly meeting time required by the problem statement. Filtering by totals greater than 20 correctly identifies meeting-heavy weeks. Counting those qualifying weeks per employee produces the required metric, and applying the threshold of at least two weeks yields exactly the desired set of employees.

Python Solution

For LeetCode Database problems, the expected answer is SQL rather than executable Python. The following Python code demonstrates the same logic programmatically.

from collections import defaultdict
from datetime import timedelta
from typing import List, Dict

class Solution:
    def findOverbookedEmployees(
        self,
        employees: List[Dict],
        meetings: List[Dict]
    ) -> List[Dict]:

        weekly_hours = defaultdict(float)

        for meeting in meetings:
            employee_id = meeting["employee_id"]
            meeting_date = meeting["meeting_date"]

            week_start = meeting_date - timedelta(days=meeting_date.weekday())

            weekly_hours[(employee_id, week_start)] += meeting["duration_hours"]

        heavy_week_count = defaultdict(int)

        for (employee_id, _), total_hours in weekly_hours.items():
            if total_hours > 20:
                heavy_week_count[employee_id] += 1

        employee_lookup = {
            employee["employee_id"]: employee
            for employee in employees
        }

        result = []

        for employee_id, count in heavy_week_count.items():
            if count >= 2:
                employee = employee_lookup[employee_id]

                result.append({
                    "employee_id": employee_id,
                    "employee_name": employee["employee_name"],
                    "department": employee["department"],
                    "meeting_heavy_weeks": count
                })

        result.sort(
            key=lambda row: (
                -row["meeting_heavy_weeks"],
                row["employee_name"]
            )
        )

        return result

The implementation first computes weekly meeting totals using a hash map keyed by (employee_id, week_start). Once all meeting records have been processed, it identifies weekly totals greater than 20 hours and increments a counter for the corresponding employee. A lookup table provides access to employee metadata, and the final result is assembled only for employees with at least two qualifying weeks. The result is then sorted according to the required ordering.

Go Solution

The actual LeetCode submission is SQL, but the following Go implementation mirrors the same logic.

package main

import (
	"sort"
	"time"
)

type Employee struct {
	EmployeeID   int
	EmployeeName string
	Department   string
}

type Meeting struct {
	EmployeeID   int
	MeetingDate  time.Time
	DurationHours float64
}

type Result struct {
	EmployeeID        int
	EmployeeName      string
	Department        string
	MeetingHeavyWeeks int
}

func findOverbookedEmployees(
	employees []Employee,
	meetings []Meeting,
) []Result {

	weeklyHours := make(map[[2]interface{}]float64)

	for _, meeting := range meetings {
		weekday := int(meeting.MeetingDate.Weekday())

		if weekday == 0 {
			weekday = 7
		}

		weekStart := meeting.MeetingDate.AddDate(0, 0, -(weekday - 1))

		key := [2]interface{}{
			meeting.EmployeeID,
			weekStart,
		}

		weeklyHours[key] += meeting.DurationHours
	}

	heavyWeekCount := make(map[int]int)

	for key, totalHours := range weeklyHours {
		if totalHours > 20 {
			employeeID := key[0].(int)
			heavyWeekCount[employeeID]++
		}
	}

	employeeLookup := make(map[int]Employee)

	for _, employee := range employees {
		employeeLookup[employee.EmployeeID] = employee
	}

	var result []Result

	for employeeID, count := range heavyWeekCount {
		if count >= 2 {
			employee := employeeLookup[employeeID]

			result = append(result, Result{
				EmployeeID:        employeeID,
				EmployeeName:      employee.EmployeeName,
				Department:        employee.Department,
				MeetingHeavyWeeks: count,
			})
		}
	}

	sort.Slice(result, func(i, j int) bool {
		if result[i].MeetingHeavyWeeks != result[j].MeetingHeavyWeeks {
			return result[i].MeetingHeavyWeeks > result[j].MeetingHeavyWeeks
		}

		return result[i].EmployeeName < result[j].EmployeeName
	})

	return result
}

The Go version follows the same structure as the Python implementation. Maps are used instead of Python dictionaries, and explicit sorting is performed with sort.Slice. Since meeting durations are decimal values, float64 is used for aggregation.

SQL Solution

WITH weekly_meetings AS (
    SELECT
        employee_id,
        DATE_SUB(
            meeting_date,
            INTERVAL WEEKDAY(meeting_date) DAY
        ) AS week_start,
        SUM(duration_hours) AS total_hours
    FROM meetings
    GROUP BY employee_id, week_start
),
meeting_heavy_weeks AS (
    SELECT
        employee_id,
        COUNT(*) AS meeting_heavy_weeks
    FROM weekly_meetings
    WHERE total_hours > 20
    GROUP BY employee_id
    HAVING COUNT(*) >= 2
)
SELECT
    e.employee_id,
    e.employee_name,
    e.department,
    mhw.meeting_heavy_weeks
FROM meeting_heavy_weeks mhw
JOIN employees e
    ON e.employee_id = mhw.employee_id
ORDER BY
    mhw.meeting_heavy_weeks DESC,
    e.employee_name ASC;

Worked Examples

Consider the example input.

Weekly Aggregation

Employee	Week Start	Meetings	Weekly Total
Alice	2023-06-05	8 + 6 + 7	21
Alice	2023-06-12	12 + 9	21
Bob	2023-06-05	15 + 8	23
Bob	2023-06-12	10	10
Carol	2023-06-05	4 + 3	7
David	2023-06-05	25	25
David	2023-06-19	22	22
Emma	2023-06-05	2	2

Identify Meeting-Heavy Weeks

Employee	Week Total	Qualifies?
Alice	21	Yes
Alice	21	Yes
Bob	23	Yes
Bob	10	No
Carol	7	No
David	25	Yes
David	22	Yes
Emma	2	No

Count Qualifying Weeks

Employee	Meeting-Heavy Weeks
Alice	2
Bob	1
David	2

Apply Final Filter

Employees with at least two meeting-heavy weeks:

Employee	Count
Alice	2
David	2

Final Sort

Both employees have the same count, so sorting falls back to employee name:

Employee
Alice Johnson
David Wilson

This matches the expected output.

Complexity Analysis

Measure	Complexity	Explanation
Time	O(M)	Each meeting record participates in aggregation exactly once
Space	O(M)	Worst case, every meeting belongs to a distinct employee-week group

The dominant operation is grouping meeting records by employee and week. The database performs a single aggregation pass over the meetings table. The number of employee-week groups cannot exceed the number of meeting records, giving an upper bound of O(M) space.

Test Cases

# Example from statement
assert solution(...) == [
    {
        "employee_id": 1,
        "employee_name": "Alice Johnson",
        "department": "Engineering",
        "meeting_heavy_weeks": 2
    },
    {
        "employee_id": 4,
        "employee_name": "David Wilson",
        "department": "Engineering",
        "meeting_heavy_weeks": 2
    }
]  # provided example

# Exactly 20 hours should not qualify
assert employee_weeks == 0  # strict > 20 condition

# One week over 20, one week under 20
assert employee_weeks == 1  # should be excluded

# Two different qualifying weeks
assert employee_weeks == 2  # should be included

# No meetings at all
assert employee_weeks == 0  # should not appear

# Multiple meetings within same week
assert weekly_total == 25  # aggregation correctness

# Three qualifying weeks
assert employee_weeks == 3  # count all qualifying weeks

# Multiple employees with same count
assert sorted_by_name is True  # secondary ordering

# Meetings spanning many calendar weeks
assert week_grouping_correct is True  # Monday-Sunday grouping

# Single employee with many meetings on same day
assert weekly_total == expected_total  # summation correctness

Test Summary

Test	Why
Provided example	Validates full workflow
Exactly 20 hours	Verifies strict comparison
One qualifying week	Verifies minimum count requirement
Two qualifying weeks	Verifies inclusion threshold
No meetings	Verifies missing employees are excluded
Multiple meetings same week	Verifies aggregation logic
Three qualifying weeks	Verifies counting behavior
Same count employees	Verifies ordering rules
Multiple calendar weeks	Verifies week calculation
Many meetings same day	Verifies summation correctness

Edge Cases

Weekly Total Equals Exactly 20 Hours

A common mistake is using >= 20 instead of > 20. The problem explicitly states that an employee is meeting-heavy only when weekly meeting hours exceed 20. A week totaling exactly 20 hours must not be counted. The SQL solution correctly uses WHERE total_hours > 20.

Multiple Meetings Within the Same Week

An employee may attend many meetings during a single week. Evaluating each meeting individually would be incorrect because qualification depends on the weekly total. The aggregation step groups by employee and week before applying the threshold, ensuring all durations are combined correctly.

Employees With No Meeting Records

Some employees may never appear in the meetings table. Such employees cannot accumulate any meeting-heavy weeks and therefore should not appear in the result. Because the solution begins from aggregated meeting data and only later joins back to employees, these employees are naturally excluded.

Employees With Only One Meeting-Heavy Week

An employee may exceed 20 hours during one week but fail to do so during all other weeks. These employees satisfy the weekly threshold but not the overall requirement of at least two meeting-heavy weeks. The HAVING COUNT(*) >= 2 clause correctly filters them out.

Week Boundary Handling

Meetings occurring on different dates may still belong to the same Monday-Sunday week. Incorrect week calculations can place meetings into the wrong groups. Using the Monday-based week start date ensures every meeting in the same calendar week is aggregated together consistently.