## Контекст (таблицы и пример данных) ### Таблицы - `orders(id, amount, status)` - `payments(id, or
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `orders(id, amount, status)`
- `payments(id, order_id, paid_amount)`
### Пример данных
**orders**
| id | amount | status |
|---:|------:|--------|
| 1 | 100 | new |
| 2 | 200 | new |
**payments**
| id | order_id | paid_amount |
|---:|---------:|------------:|
| 10 | 1 | 100 |
| 11 | 2 | 50 |
---
## Задача
Сформировать запрос, который возвращает вычисленный статус:
- `paid` если `sum(paid_amount) >= orders.amount`
- `partially_paid` если `0 < sum < amount`
- `not_paid` если `sum = 0`
Вернуть: `order_id`, `computed_status`.
Ответы
```sql
SELECT
o.id AS order_id,
CASE
WHEN COALESCE(SUM(p.paid_amount), 0) >= o.amount THEN 'paid'
WHEN COALESCE(SUM(p.paid_amount), 0) > 0 THEN 'partially_paid'
ELSE 'not_paid'
END AS computed_status
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
GROUP BY o.id, o.amount
ORDER BY o.id;
```