## Контекст (таблицы и пример данных) ### Таблицы - `sensor_readings(sensor_id, reading_time, value
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `sensor_readings(sensor_id, reading_time, value)`
### Пример данных
**sensor_readings**
| sensor_id | reading_time | value |
|----------:|----------------------|------:|
| 1 | 2025-01-01 10:00:00 | 10 |
| 1 | 2025-01-01 10:05:00 | 11 |
| 1 | 2025-01-01 10:30:00 | 12 |
---
## Задача
Для каждого датчика найти интервалы между соседними измерениями, где разрыв > 15 минут.
Вернуть: `sensor_id`, `gap_start`, `gap_end`, `gap_minutes`.
Ответы
```sql
WITH w AS (
SELECT
sensor_id,
reading_time,
LAG(reading_time) OVER (PARTITION BY sensor_id ORDER BY reading_time) AS prev_time
FROM sensor_readings
)
SELECT
sensor_id,
prev_time AS gap_start,
reading_time AS gap_end,
EXTRACT(EPOCH FROM (reading_time - prev_time)) / 60.0 AS gap_minutes
FROM w
WHERE prev_time IS NOT NULL
AND reading_time - prev_time > INTERVAL '15 minutes'
ORDER BY sensor_id, gap_start;
```