LeetCode-in-Java

3611. Find Overbooked Employees

Medium

Table: employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| department    | varchar |
+---------------+---------+
employee_id is the unique identifier for this table.
Each row contains information about an employee and their department. 

Table: meetings

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| meeting_id    | int     |
| employee_id   | int     |
| meeting_date  | date    |
| meeting_type  | varchar |
| duration_hours| decimal |
+---------------+---------+
meeting_id is the unique identifier for this table.
Each row represents a meeting attended by an employee. meeting_type can be 'Team', 'Client', or 'Training'. 

Write a solution to find employees who are meeting-heavy - employees who spend more than 50% of their working time in meetings during any given week.

Return the result table ordered by the number of meeting-heavy weeks in descending order, then by employee name in ascending order.

The result format is in the following example.

Example:

Input:

employees table:

+-------------+----------------+-------------+
| employee_id | employee_name  | department  |
+-------------+----------------+-------------+
| 1           | Alice Johnson  | Engineering |
| 2           | Bob Smith      | Marketing   |
| 3           | Carol Davis    | Sales       |
| 4           | David Wilson   | Engineering |
| 5           | Emma Brown     | HR          |
+-------------+----------------+-------------+ 

meetings table:

+------------+-------------+--------------+--------------+----------------+
| meeting_id | employee_id | meeting_date | meeting_type | duration_hours |
+------------+-------------+--------------+--------------+----------------+
| 1          | 1           | 2023-06-05   | Team         | 8.0            |
| 2          | 1           | 2023-06-06   | Client       | 6.0            |
| 3          | 1           | 2023-06-07   | Training     | 7.0            |
| 4          | 1           | 2023-06-12   | Team         | 12.0           |
| 5          | 1           | 2023-06-13   | Client       | 9.0            |
| 6          | 2           | 2023-06-05   | Team         | 15.0           |
| 7          | 2           | 2023-06-06   | Client       | 8.0            |
| 8          | 2           | 2023-06-12   | Training     | 10.0           |
| 9          | 3           | 2023-06-05   | Team         | 4.0            |
| 10         | 3           | 2023-06-06   | Client       | 3.0            |
| 11         | 4           | 2023-06-05   | Team         | 25.0           |
| 12         | 4           | 2023-06-19   | Client       | 22.0           |
| 13         | 5           | 2023-06-05   | Training     | 2.0            |
+------------+-------------+--------------+--------------+----------------+ 

Output:

+-------------+---------------+-------------+---------------------+
| employee_id | employee_name | department  | meeting_heavy_weeks |
+-------------+---------------+-------------+---------------------+
| 1           | Alice Johnson | Engineering | 2                   |
| 4           | David Wilson  | Engineering | 2                   |
+-------------+---------------+-------------+---------------------+ 

Explanation:

The result table is ordered by meeting_heavy_weeks in descending order, then by employee name in ascending order.

Solution

# Write your MySQL query statement below
WITH process_1 AS (
    SELECT 
        employee_id, 
        SUM(duration_hours) AS duration_total
    FROM 
        meetings
    GROUP BY 
        employee_id, 
        WEEKOFYEAR(meeting_date), 
        YEAR(meeting_date)
)
SELECT 
    p.employee_id, 
    e.employee_name, 
    e.department, 
    COUNT(p.employee_id) AS meeting_heavy_weeks
FROM 
    process_1 p
    INNER JOIN employees e ON p.employee_id = e.employee_id
WHERE 
    duration_total > 20
GROUP BY 
    p.employee_id, 
    e.employee_name, 
    e.department
HAVING 
    COUNT(p.employee_id) > 1
ORDER BY 
    meeting_heavy_weeks DESC, 
    employee_name ASC;