3611\. Find Overbooked Employees

Medium

Table:`employees`

Table:`meetings`

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.

* Assume a standard work week is`40`**hours**

* Calculate**total meeting hours** per employee**per week** (**Monday to Sunday**)

* An employee is meeting-heavy if their weekly meeting hours`>``20` hours (`50%` of`40` hours)

* Count how many weeks each employee was meeting-heavy

***Only include** employees who were meeting-heavy for**at least**`2`**weeks**

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:

meetings table:

**Output:**

**Explanation:**

***Alice Johnson (employee\_id = 1):**

* Week of June 5-11 (2023-06-05 to 2023-06-11): 8.0 + 6.0 + 7.0 = 21.0 hours (> 20 hours)

* Week of June 12-18 (2023-06-12 to 2023-06-18): 12.0 + 9.0 = 21.0 hours (> 20 hours)

* Meeting-heavy for 2 weeks

***David Wilson (employee\_id = 4):**

* Week of June 5-11: 25.0 hours (> 20 hours)

* Week of June 19-25: 22.0 hours (> 20 hours)

* Meeting-heavy for 2 weeks

***Employees not included:**

* Bob Smith (employee\_id = 2): Week of June 5-11: 15.0 + 8.0 = 23.0 hours (> 20), Week of June 12-18: 10.0 hours (< 20). Only 1 meeting-heavy week

* Carol Davis (employee\_id = 3): Week of June 5-11: 4.0 + 3.0 = 7.0 hours (< 20). No meeting-heavy weeks

* Emma Brown (employee\_id = 5): Week of June 5-11: 2.0 hours (< 20). No meeting-heavy weeks

The result table is ordered by meeting\_heavy\_weeks in descending order, then by employee name in ascending order.

WITH process_1AS (

employee_id,

SUM(duration_hours)AS duration_total

meetings

employee_id,

WEEKOFYEAR(meeting_date),

YEAR(meeting_date)

)

p.employee_id,

e.employee_name,

e.department,

COUNT(p.employee_id)AS meeting_heavy_weeks

process_1 p

INNER JOIN employees eONp.employee_id=e.employee_id

duration_total>20

p.employee_id,

e.employee_name,

e.department

COUNT(p.employee_id)>1

meeting_heavy_weeksDESC,

employee_nameASC;

packageg3601_3700.s3611_find_overbooked_employees;

importstaticorg.hamcrest.CoreMatchers.equalTo;

importstaticorg.hamcrest.MatcherAssert.assertThat;

importjava.io.BufferedReader;

importjava.io.FileNotFoundException;

importjava.io.FileReader;

importjava.sql.Connection;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.sql.Statement;

importjava.util.stream.Collectors;

importjavax.sql.DataSource;

importorg.junit.jupiter.api.Test;

importorg.zapodot.junit.db.annotations.EmbeddedDatabase;

importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;

importorg.zapodot.junit.db.common.CompatibilityMode;

@EmbeddedDatabaseTest(

compatibilityMode =CompatibilityMode.MySQL,

initialSqls =

+" employee_id INTEGER,"

+" employee_name VARCHAR(50),"

+" department VARCHAR(50)"

+");"

+"INSERT INTO employees (employee_id, employee_name, department) VALUES"

+" (1, 'Alice Johnson', 'Engineering'),"

+" (2, 'Bob Smith', 'Marketing'),"

+" (3, 'Carol Davis', 'Sales'),"

+" (4, 'David Wilson', 'Engineering'),"

+" (5, 'Emma Brown', 'HR');"

+"CREATE TABLE meetings ("

+" meeting_id INTEGER,"

+" employee_id INTEGER,"

+" meeting_date DATE,"

+" meeting_type VARCHAR(20),"

+" duration_hours DECIMAL(4,1)"

+");"

+"INSERT INTO meetings (meeting_id, employee_id, "

+"meeting_date, meeting_type, duration_hours) VALUES"

+" (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);")

classMysqlTest {

voidtestScript(@EmbeddedDatabaseDataSourcedataSource)

throwsSQLException,FileNotFoundException {

try (finalConnectionconnection =dataSource.getConnection()) {

try (finalStatementstatement =connection.createStatement();

finalResultSetresultSet =

statement.executeQuery(

newBufferedReader(

newFileReader(

+"s3611_find_overbooked_employees/"

+"script.sql"))

.lines()

.collect(Collectors.joining("\n"))

.replaceAll("#.*?\\r?\\n","")

.replace("WEEKOFYEAR","ISO_WEEK"))) {

assertThat(resultSet.next(),equalTo(true));

assertThat(resultSet.getNString(1),equalTo("1"));

assertThat(resultSet.getNString(2),equalTo("Alice Johnson"));

assertThat(resultSet.getNString(3),equalTo("Engineering"));

assertThat(resultSet.getNString(4),equalTo("2"));

assertThat(resultSet.next(),equalTo(true));

assertThat(resultSet.getNString(1),equalTo("4"));

assertThat(resultSet.getNString(2),equalTo("David Wilson"));

assertThat(resultSet.getNString(3),equalTo("Engineering"));

assertThat(resultSet.getNString(4),equalTo("2"));

assertThat(resultSet.next(),equalTo(false));

}

}

}

}