## Контекст (таблицы и пример данных) ### Таблицы - `trips(id, driver_id, start_time, fare_amount)`
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `trips(id, driver_id, start_time, fare_amount)`
- `driver_costs(driver_id, cost_day, cost_amount)`
### Пример данных
**trips**
| id | driver_id | start_time | fare_amount |
|---:|----------:|---------------------|------------:|
| 1 | 7 | 2025-01-01 10:00:00 | 20 |
| 2 | 7 | 2025-01-01 12:00:00 | 10 |
**driver_costs**
| driver_id | cost_day | cost_amount |
|----------:|------------|------------:|
| 7 | 2025-01-01 | 40 |
---
## Задача
Посчитать выручку по дням и найти дни, где `revenue - costs < 0`.
Вернуть: `driver_id`, `day`, `revenue`, `costs`, `margin`.
Ответы
```sql
WITH rev AS (
SELECT driver_id, start_time::date AS day, SUM(fare_amount) AS revenue
FROM trips
GROUP BY driver_id, start_time::date
), c AS (
SELECT driver_id, cost_day AS day, SUM(cost_amount) AS costs
FROM driver_costs
GROUP BY driver_id, cost_day
)
SELECT
COALESCE(rev.driver_id, c.driver_id) AS driver_id,
COALESCE(rev.day, c.day) AS day,
COALESCE(rev.revenue, 0) AS revenue,
COALESCE(c.costs, 0) AS costs,
COALESCE(rev.revenue, 0) - COALESCE(c.costs, 0) AS margin
FROM rev
FULL JOIN c ON c.driver_id = rev.driver_id AND c.day = rev.day
WHERE COALESCE(rev.revenue, 0) - COALESCE(c.costs, 0) < 0
ORDER BY driver_id, day;
```