3611. Find Overbooked Employees

Tables

erDiagram
	employees {
		employee_id int PK
		employee_name varchar
		department varchar
	}
	meetings {
		meeting_id int PK
		employee_id int FK
		meeting_date date
		meeting_type varchar "Team, Client, Training"
		duration_hours decimal
	}
	employees || -- o{ meetings : "make meetings"
Mermaid
복사

Question

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.

DataFlow

per week 구분 주단위 20시간 이상 구분 -> count -> 2주이상 해당되는 직원

Answer

WITH week_mt AS (
	SELECT
		*,
		DATE_FORMAT(meeting_date, "%u") AS weeks
	FROM meetings
),

week_mt_time AS (
	SELECT
		employee_id,
		weeks,
		SUM(duration_hours) AS mt_time
	FROM week_mt
	GROUP BY employee_id, weeks
	HAVING mt_time > 20
),

week_cnt AS (
	SELECT
		employee_id,
		COUNT(weeks) AS wk_cnt
		FROM week_mt_time
	GROUP BY employee_id
	HAVING wk_cnt >= 2
)

SELECT
	w.employee_id,
	e.employee_name,
	e.department,
	w.wk_cnt AS meeting_heavy_weeks
FROM week_cnt w
INNER JOIN employees e
	ON w.employee_id = e.employee_id
ORDER BY meeting_heavy_weeks DESC, e.employee_name ASC;
SQL
복사

![[Pasted image 20251024084107.png]] 문제를 풀때는 가독성을 위해서 주로 CTE를 사용해서 푸는데 재사용을 하지 않는다면, 쿼리 효율성은 subquery가 좋고 이번 문제를 풀면서는 주단위 구분을 몰라서 구글링했다.

subquery -> having, where

SELECT
	w.employee_id,
	e.employee_name,
	e.department,
	w.wk_cnt AS meeting_heavy_weeks
FROM (
	SELECT
		employee_id,
		COUNT(weeks) AS wk_cnt
	FROM (
		SELECT
			employee_id,
			weeks,
			SUM(duration_hours) AS mt_time
		FROM (
			SELECT
				*,
				DATE_FORMAT(meeting_date, "%u") AS weeks
				FROM meetings
		) week_mt
		GROUP BY employee_id, weeks
	) week_mt_time
	WHERE mt_time > 20
	GROUP BY employee_id
) w
INNER JOIN employees e
	ON w.employee_id = e.employee_id AND w.wk_cnt >= 2
ORDER BY meeting_heavy_weeks DESC, e.employee_name ASC;
SQL
복사

WITH process_1 AS (
	SELECT
		employee_id,
		SUM(duration_hours) 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) 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;
SQL
복사

WEEKOFYEAR, YEAR 1년 중 주 단위 추출 함수, 연도가 바뀔 수도 있으니 YEAR와 함께 group by