У вас есть таблица
sales:
CREATE TABLE sales (
salesman_id NUMBER,
region VARCHAR2(50),
amount NUMBER
);
Данные:
| salesman_id | region | amount |
|-------------|------------|--------|
| 101 | 'North' | 200 |
| 101 | 'North' | NULL |
| 102 | 'North' | 150 |
| 103 | 'North' | NULL |
| 104 | 'South' | 300 |
| 105 | 'South' | NULL |
🎯 Задача:
Вывести
salesman_id тех продавцов, чья сумма продаж в своём регионе меньше средней по региону, учитывая только те записи, где `amount` не NULL. Но — обязательно включать продавцов, у которых все продажи NULL, и считать, что их сумма равна 0.
---
### ❗ Подвохы:
-
SUM() и AVG() игнорируют NULL, но если у человека *все* значения NULL, SUM вернёт NULL.- Нужно сравнивать 0 с
AVG, а не NULL.- Надо корректно сгруппировать по региону и учитывать, где NULL'ы не попадают в
AVG.---
✅ Решение:
```sql
SELECT s.salesman_id
FROM (
SELECT
salesman_id,
region,
NVL(SUM(amount), 0) AS total_sales
FROM sales
GROUP BY salesman_id, region
) s
JOIN (
SELECT
region,
AVG(amount) AS avg_region_sales
FROM sales
WHERE amount IS NOT NULL
GROUP BY region
) r
ON s.region = r.region
WHERE s.total_sales < r.avg_region_sales;
```
🧠 Разбор:
1. В подзапросе `s`:
- `SUM(amount)` по продавцу → `NULL`, если продаж нет
- `NVL(..., 0)` превращает такие NULL в 0
2. В подзапросе `r`:
- `AVG(amount)` по региону, игнорируя NULL
- Только валидные продажи участвуют в средней
3. Сравниваем: **продавцы с 0 продаж тоже идут в сравнение**
💡 Вывод:
Этот запрос:
- корректно считает продажи даже для "нулевых" продавцов
- использует `NVL()` и фильтрацию `WHERE amount IS NOT NULL`
- демонстрирует знание поведения агрегатных функций и подзапросов
👀 На собеседованиях часто забывают, что `SUM(NULL)` даёт `NULL`, и сравнение с `AVG` не срабатывает без `NVL`.
➡ SQL Community | Чат