## Контекст (таблицы и пример данных) ### Таблицы - `trips(id, area, status)` > `status` ∈ {`compl
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `trips(id, area, status)`
> `status` ∈ {`completed`, `cancelled`}.
### Пример данных
**trips**
| id | area | status |
|---:|--------|-----------|
| 1 | Center | cancelled |
| 2 | Center | completed |
| 3 | North | cancelled |
---
## Задача
Посчитать cancellation rate по району и отранжировать районы по доле отмен.
Вернуть: `area`, `cancel_rate`, `rank`.
Ответы
```sql
WITH a AS (
SELECT
area,
COUNT(*) FILTER (WHERE status = 'cancelled')::numeric / COUNT(*) AS cancel_rate
FROM trips
GROUP BY area
)
SELECT area, cancel_rate,
RANK() OVER (ORDER BY cancel_rate DESC) AS rank
FROM a
ORDER BY rank, area;
```