## Контекст (таблицы и пример данных) ### Таблицы - `trips(id, driver_id, start_time, end_time)` #
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `trips(id, driver_id, start_time, end_time)`
### Пример данных
**trips**
| id | driver_id | start_time | end_time |
|---:|----------:|---------------------|---------------------|
| 1 | 7 | 2025-01-01 10:00:00 | 2025-01-01 10:10:00 |
| 2 | 7 | 2025-01-01 10:35:00 | 2025-01-01 10:50:00 |
| 3 | 7 | 2025-01-01 11:05:00 | 2025-01-01 11:20:00 |
---
## Задача
Сгруппировать поездки водителя в «сессии»: новая сессия начинается, если разрыв между `prev_end_time` и `start_time` > 30 минут.
Вернуть: `driver_id`, `trip_id`, `session_id` (нумерация с 1).
Ответы
```sql
WITH w AS (
SELECT
t.*,
CASE
WHEN LAG(end_time) OVER (PARTITION BY driver_id ORDER BY start_time) IS NULL THEN 1
WHEN start_time - LAG(end_time) OVER (PARTITION BY driver_id ORDER BY start_time) > INTERVAL '30 minutes' THEN 1
ELSE 0
END AS is_new
FROM trips t
), s AS (
SELECT
w.*,
SUM(is_new) OVER (PARTITION BY driver_id ORDER BY start_time) AS session_id
FROM w
)
SELECT driver_id, id AS trip_id, session_id
FROM s
ORDER BY driver_id, trip_id;
```