Welcome to Subscribe On Youtube

3611. Find Overbooked Employees

Description

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.

• 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:

+-+-+-+
\| 1           \| Alice Johnson  \| Engineering \|
\| 2           \| Bob Smith      \| Marketing   \|
\| 3           \| Carol Davis    \| Sales       \|
\| 4           \| David Wilson   \| Engineering \|
\| 5           \| Emma Brown     \| HR          \|
+-+-+
\| meeting_id \| employee_id \| meeting_date \| meeting_type \| duration_hours \|
++-+--+--+-+

Output:

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

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.

Solutions

Solution 1: Group Aggregation + Join Query

First, we group the data by employee_id, year, and week to calculate the total meeting hours for each employee in each week. Then, we filter out the weeks where the meeting hours exceed 20 and count the number of meeting-heavy weeks for each employee. Finally, we join the result with the employees table, filter out employees with at least 2 meeting-heavy weeks, and sort the results as required.

• Python
• SQL

• import pandas as pd
  
  
  def find_overbooked_employees(
      employees: pd.DataFrame, meetings: pd.DataFrame
  ) -> pd.DataFrame:
      meetings["meeting_date"] = pd.to_datetime(meetings["meeting_date"])
      meetings["year"] = meetings["meeting_date"].dt.isocalendar().year
      meetings["week"] = meetings["meeting_date"].dt.isocalendar().week
  
      week_meeting_hours = (
          meetings.groupby(["employee_id", "year", "week"], as_index=False)[
              "duration_hours"
          ]
          .sum()
          .rename(columns={"duration_hours": "hours"})
      )
  
      intensive_weeks = week_meeting_hours[week_meeting_hours["hours"] >= 20]
  
      intensive_count = (
          intensive_weeks.groupby("employee_id")
          .size()
          .reset_index(name="meeting_heavy_weeks")
      )
  
      result = intensive_count.merge(employees, on="employee_id")
  
      result = result[result["meeting_heavy_weeks"] >= 2]
  
      result = result.sort_values(
          ["meeting_heavy_weeks", "employee_name"], ascending=[False, True]
      )
  
      return result[
          ["employee_id", "employee_name", "department", "meeting_heavy_weeks"]
      ].reset_index(drop=True)
  
  

• # Write your MySQL query statement below
  WITH
      week_meeting_hours AS (
          SELECT
              employee_id,
              YEAR(meeting_date) AS year,
              WEEK(meeting_date, 1) AS week,
              SUM(duration_hours) hours
          FROM meetings
          GROUP BY 1, 2, 3
      ),
      intensive_weeks AS (
          SELECT
              employee_id,
              employee_name,
              department,
              count(1) AS meeting_heavy_weeks
          FROM
              week_meeting_hours
              JOIN employees USING (employee_id)
          WHERE hours >= 20
          GROUP BY 1
      )
  SELECT employee_id, employee_name, department, meeting_heavy_weeks
  FROM intensive_weeks
  WHERE meeting_heavy_weeks >= 2
  ORDER BY 4 DESC, 2;
  
  

All Problems

All Solutions