## Контекст (таблицы и пример данных) ### Таблицы - `timesheet(employee_id, work_day, hours)` ###
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `timesheet(employee_id, work_day, hours)`
### Пример данных
**timesheet**
| employee_id | work_day | hours |
|-----------:|------------|------:|
| 1 | 2025-01-01 | 8 |
| 1 | 2025-01-03 | 8 |
---
## Задача
Для `employee_id = 1` найти рабочие дни, где нет записи в табеле между min и max датой (шаг 1 день).
Вернуть: `missing_day`.
Ответы
```sql
WITH bounds AS (
SELECT MIN(work_day) AS d1, MAX(work_day) AS d2
FROM timesheet
WHERE employee_id = 1
), days AS (
SELECT generate_series((SELECT d1 FROM bounds), (SELECT d2 FROM bounds), INTERVAL '1 day')::date AS d
)
SELECT d AS missing_day
FROM days
LEFT JOIN timesheet t
ON t.employee_id = 1 AND t.work_day = d
WHERE t.work_day IS NULL
ORDER BY missing_day;
```