## Контекст (таблицы и пример данных) ### Таблицы - `batches(batch_id, product_id, received_at, qty
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `batches(batch_id, product_id, received_at, qty)`
- `shipments(ship_id, product_id, shipped_at, qty)`
### Пример данных
**batches**
| batch_id | product_id | received_at | qty |
|---------:|-----------:|------------|----:|
| 1 | 10 | 2025-01-01 | 5 |
| 2 | 10 | 2025-01-05 | 10 |
**shipments**
| ship_id | product_id | shipped_at | qty |
|-------:|-----------:|-----------:|---:|
| 100 | 10 | 2025-01-06 | 6 |
---
## Задача
Распределить отгрузки по партиям по FIFO и вывести, сколько списано из каждой партии.
Вернуть: `ship_id`, `batch_id`, `consumed_qty`.
*(Допускается решение в PostgreSQL с использованием оконных функций и диапазонов.)*
Ответы
```sql
WITH b AS (
SELECT
batch_id, product_id, received_at, qty,
SUM(qty) OVER (PARTITION BY product_id ORDER BY received_at, batch_id) AS b_cum,
SUM(qty) OVER (PARTITION BY product_id ORDER BY received_at, batch_id) - qty AS b_prev
FROM batches
), s AS (
SELECT
ship_id, product_id, shipped_at, qty,
SUM(qty) OVER (PARTITION BY product_id ORDER BY shipped_at, ship_id) AS s_cum,
SUM(qty) OVER (PARTITION BY product_id ORDER BY shipped_at, ship_id) - qty AS s_prev
FROM shipments
)
SELECT
s.ship_id,
b.batch_id,
GREATEST(0,
LEAST(b.b_cum, s.s_cum) - GREATEST(b.b_prev, s.s_prev)
) AS consumed_qty
FROM s
JOIN b USING (product_id)
WHERE LEAST(b.b_cum, s.s_cum) > GREATEST(b.b_prev, s.s_prev)
ORDER BY s.ship_id, b.batch_id;
```