## Контекст (таблицы и пример данных) ### Таблицы - `order_status_history(order_id, changed_at, sta
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `order_status_history(order_id, changed_at, status)`
### Пример данных
**order_status_history**
| order_id | changed_at | status |
|---------:|---------------------|--------|
| 1 | 2025-01-01 10:00:00 | new |
| 1 | 2025-01-01 11:00:00 | new |
| 1 | 2025-01-01 12:00:00 | paid |
---
## Задача
Для каждого заказа найти максимальную длину подряд идущих записей с одинаковым `status` (в порядке `changed_at`).
Вернуть: `order_id`, `status`, `streak_len` (максимальная).
Ответы
```sql
WITH w AS (
SELECT
h.*,
CASE
WHEN LAG(status) OVER (PARTITION BY order_id ORDER BY changed_at, status) IS DISTINCT FROM status
THEN 1 ELSE 0
END AS is_new
FROM order_status_history h
), g AS (
SELECT
*,
SUM(is_new) OVER (PARTITION BY order_id ORDER BY changed_at, status) AS grp
FROM w
), streaks AS (
SELECT order_id, status, grp, COUNT(*) AS streak_len
FROM g
GROUP BY order_id, status, grp
)
SELECT order_id, status, MAX(streak_len) AS streak_len
FROM streaks
GROUP BY order_id, status
ORDER BY order_id, streak_len DESC;
```