← К задачам SQL
← Предыдущая Следующая →

306. Самая длинная серия рабочих дней

Expert 0 решили серии

Для каждого сотрудника, у которого есть строки в worklog, найдите самую длинную серию календарно подряд идущих рабочих дней. Пропущенный день разрывает серию, даже если это выходной в календаре.

Если у человека несколько серий одной длины, возьмите ту, что началась раньше. Сотрудников без табеля не выводите.

Используйте таблицы worklog и employees.

Результат: employee, streak_days, start_date, end_date. Сначала длиннее серия, при равенстве — по имени.

Схема базы команды

Схема БД состоит из пяти отношений:

departments (id, name)

employees (id, name, department_id, manager_id, hired_on, salary)

projects (id, name, start_date, end_date)

assignments (employee_id, project_id, role, from_date, to_date)

worklog (id, employee_id, work_date, hours, project_id)

employees.manager_id ссылается на ту же таблицу: дерево подчинения. У руководителя компании manager_id равен NULL. Назначение на проект — отрезок дат включительно с обеих сторон. Один человек может быть на нескольких проектах сразу, если интервалы пересекаются. worklog — фактические рабочие дни; пропуск даты разрывает серию.

Схема данных: departments, employees, projects, assignments, worklog departments Column Name Data Type Nulls id int name text employees Column Name Data Type Nulls id int name text department_id int manager_id int YES hired_on date salary numeric projects Column Name Data Type Nulls id int name text start_date date end_date date assignments Column Name Data Type Nulls employee_id int project_id int role text from_date date to_date date worklog Column Name Data Type Nulls id int employee_id int work_date date hours int project_id int

Данные таблиц

Первые 5 строк каждой таблицы

departments 4

id name
1 Управление
2 Разработка
3 Аналитика
4 Поддержка

employees 13

id name department_id manager_id hired_on salary
1 Ирина Белова 1 None 2019-01-10 280000
2 Павел Чернов 2 1 2020-03-01 210000
3 Елена Юрченко 3 1 2020-05-20 190000
4 Анна Ким 2 2 2021-06-15 220000
5 Олег Сафин 2 2 2021-09-01 150000

projects 6

id name start_date end_date
1 Альфа 2024-01-01 2024-06-30
2 Бета 2024-03-01 2024-12-31
3 Гамма 2024-07-01 2024-09-30
4 Дельта 2024-02-01 2024-04-30
5 Омега 2024-08-01 2024-11-30

assignments 17

employee_id project_id role from_date to_date
2 1 lead 2024-01-01 2024-06-30
3 3 lead 2024-07-01 2024-09-30
4 1 dev 2024-01-01 2024-06-30
4 2 dev 2024-03-01 2024-12-31
4 6 dev 2024-03-15 2024-04-30

worklog 30

id employee_id work_date hours project_id
1 2 2024-03-04 8 1
2 2 2024-03-05 8 1
3 2 2024-03-06 8 1
4 2 2024-03-09 8 1
5 4 2024-03-04 8 1

Данные таблиц

Первые 5 строк. Полная схема — по кнопке выше.

departments 4

id name
1 Управление
2 Разработка
3 Аналитика
4 Поддержка

employees 13

id name department_id manager_id hired_on salary
1 Ирина Белова 1 None 2019-01-10 280000
2 Павел Чернов 2 1 2020-03-01 210000
3 Елена Юрченко 3 1 2020-05-20 190000
4 Анна Ким 2 2 2021-06-15 220000
5 Олег Сафин 2 2 2021-09-01 150000

projects 6

id name start_date end_date
1 Альфа 2024-01-01 2024-06-30
2 Бета 2024-03-01 2024-12-31
3 Гамма 2024-07-01 2024-09-30
4 Дельта 2024-02-01 2024-04-30
5 Омега 2024-08-01 2024-11-30

assignments 17

employee_id project_id role from_date to_date
2 1 lead 2024-01-01 2024-06-30
3 3 lead 2024-07-01 2024-09-30
4 1 dev 2024-01-01 2024-06-30
4 2 dev 2024-03-01 2024-12-31
4 6 dev 2024-03-15 2024-04-30

worklog 30

id employee_id work_date hours project_id
1 2 2024-03-04 8 1
2 2 2024-03-05 8 1
3 2 2024-03-06 8 1
4 2 2024-03-09 8 1
5 4 2024-03-04 8 1

Комментарии (0)

Зарегистрируйтесь или войдите, чтобы оставить комментарий

Пока нет комментариев. Будьте первым!