## Контекст (таблицы и пример данных) ### Таблицы - `downtimes(server_id, start_ts, end_ts)` ### П
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `downtimes(server_id, start_ts, end_ts)`
### Пример данных
**downtimes**
| server_id | start_ts | end_ts |
|----------:|--------------------|--------------------|
| 1 | 2025-01-01 10:00:00| 2025-01-01 11:00:00|
| 1 | 2025-01-01 10:30:00| 2025-01-01 12:00:00|
| 1 | 2025-01-01 13:00:00| 2025-01-01 14:00:00|
---
## Задача
Объединить пересекающиеся интервалы даунтайма для каждого `server_id`.
Вернуть: `server_id`, `merged_start`, `merged_end`.
Ответы
```sql
WITH ordered AS (
SELECT
d.*,
MAX(end_ts) OVER (PARTITION BY server_id ORDER BY start_ts, end_ts
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_end
FROM downtimes d
), marks AS (
SELECT
*,
CASE WHEN prev_max_end IS NULL OR start_ts > prev_max_end THEN 1 ELSE 0 END AS is_new
FROM ordered
), grp AS (
SELECT
*,
SUM(is_new) OVER (PARTITION BY server_id ORDER BY start_ts, end_ts) AS g
FROM marks
)
SELECT server_id,
MIN(start_ts) AS merged_start,
MAX(end_ts) AS merged_end
FROM grp
GROUP BY server_id, g
ORDER BY server_id, merged_start;
```