🧩 Интересная SQL-задача: «Вечные работники»

Представим, что у вас есть таблица employees, где хранится история переводов сотрудников между отделами:

CREATE TABLE employees (
    employee_id INT,
    department VARCHAR(50),
    start_date DATE,
    end_date DATE
); 

WITH ordered_periods AS (
  SELECT *,
         LAG(end_date) OVER (PARTITION BY employee_id, department ORDER BY start_date) AS prev_end
  FROM employees
),
grouped_periods AS (
  SELECT *,
         CASE
           WHEN prev_end IS NULL OR prev_end + INTERVAL '1 day' < start_date THEN 1
           ELSE 0
         END AS is_new_group
  FROM ordered_periods
),
group_tags AS (
  SELECT *,
         SUM(is_new_group) OVER (PARTITION BY employee_id, department ORDER BY start_date) AS group_id
  FROM grouped_periods
),
grouped_ranges AS (
  SELECT employee_id, department, group_id,
         MIN(start_date) AS period_start,
         MAX(COALESCE(end_date, CURRENT_DATE)) AS period_end
  FROM group_tags
  GROUP BY employee_id, department, group_id
),
long_periods AS (
  SELECT employee_id, department, period_start, period_end,
         (period_end - period_start) AS duration_days
  FROM grouped_ranges
  WHERE period_end - period_start > INTERVAL '3 years'
)
SELECT *
FROM long_periods;

Data Science. SQL hub

🧩 Интересная SQL-задача: «Вечные работники»

Представим, что у вас есть таблица employees, где хранится история переводов сотрудников между отделами:

CREATE TABLE employees (
    employee_id INT,
    department VARCHAR(50),
    start_date DATE,
    end_date DATE
); 

Пример данных:

employee_id department start_date end_date
1 Sales 2020-01-01 2022-01-01
1 HR 2022-01-02 NULL
2 Sales 2019-05-01 2021-05-01
2 Sales 2021-05-02 NULL
3 HR 2022-06-01 NULL

🎯 Задача: Найдите всех сотрудников, которые работали в одном и том же отделе без перерыва более 3 лет.
Если человек работал в Sales 2+ периода подряд — они считаются одним, если не было пропуска между ними.

🛠 Решение на PostgreSQL:

WITH ordered_periods AS (
  SELECT *,
         LAG(end_date) OVER (PARTITION BY employee_id, department ORDER BY start_date) AS prev_end
  FROM employees
),
grouped_periods AS (
  SELECT *,
         CASE
           WHEN prev_end IS NULL OR prev_end + INTERVAL '1 day' < start_date THEN 1
           ELSE 0
         END AS is_new_group
  FROM ordered_periods
),
group_tags AS (
  SELECT *,
         SUM(is_new_group) OVER (PARTITION BY employee_id, department ORDER BY start_date) AS group_id
  FROM grouped_periods
),
grouped_ranges AS (
  SELECT employee_id, department, group_id,
         MIN(start_date) AS period_start,
         MAX(COALESCE(end_date, CURRENT_DATE)) AS period_end
  FROM group_tags
  GROUP BY employee_id, department, group_id
),
long_periods AS (
  SELECT employee_id, department, period_start, period_end,
         (period_end - period_start) AS duration_days
  FROM grouped_ranges
  WHERE period_end - period_start > INTERVAL '3 years'
)
SELECT *
FROM long_periods;

🔍 Разбор логики:

• Сначала находим предыдущие даты окончания для сравнения.
• Метим, где начинается новая непрерывная группа.
• Суммируем метки — получаем уникальные группы без разрывов.
• Группируем и считаем длительность.
• Оставляем только тех, кто проработал более 3 лет подряд в одном отделе.

📌 Такая задача хороша для собеседований: проверяет оконные функции, интервалы и группировки по логике, а не только по ключам.

@sqlhub

hottg.com/sqlhub/1924

2.6K viewsJun 21 at 10:02