🔥 Live Coding: зарплаты и средний доход по отделам

Задача: Есть таблица employee_salary:

• employee_id • salary_rur • bonus_rur • department_id

Нужно для каждого отдела посчитать:

• общее количество сотрудников • количество сотрудников с заполненной зарплатой • средний доход сотрудника

Где: доход = salary + bonus

⚠️ Важно: • если salary_rur is null → считаем как 0 • если bonus_rur is null → считаем как 0 • средний доход считаем только по тем сотрудникам, у которых есть хотя бы одно значение: salary_rur is not null или bonus_rur is not null

На выходе хотим получить:

• department_id • employees_cnt • employees_with_salary_cnt • avg_income

Решение:

select department_id, count() as employees_cnt, countIf(salary_rur is not null) as employees_with_salary_cnt, avg( case when salary_rur is not null or bonus_rur is not null then ifNull(salary_rur, 0) + ifNull(bonus_rur, 0) else null end ) as avg_income from employee_salary group by department_id order by department_id

🧠 Как здесь думаем:

Сначала считаем общее количество сотрудников в отделе. Это обычный count().

Дальше считаем количество сотрудников с заполненной зарплатой. Здесь важна именно заполненная salary_rur, поэтому используем:

countIf(salary_rur is not null)

Теперь самое важное — avg_income.

По условию: • null в salary считаем как 0 • null в bonus считаем как 0 • но если у сотрудника оба поля null, такого сотрудника в средний доход включать нельзя

Поэтому логика такая:

если есть хотя бы одно значение, считаем доход как:

ifNull(salary_rur, 0) + ifNull(bonus_rur, 0)

если оба поля null, возвращаем null

Почему это важно? Потому что avg() не учитывает null, а значит такие сотрудники автоматически не попадут в расчет среднего ✅

⚠️ Где можно ошибиться:

Просто написать:

avg(ifNull(salary_rur, 0) + ifNull(bonus_rur, 0))

Тогда сотрудники, у которых оба поля null, попадут в средний как 0. Это исказит результат ❌

Посчитать employees_with_salary_cnt через count()

Тогда туда попадут вообще все сотрудники, а нам нужны только те, у кого salary_rur заполнена.

Забыть, что bonus_rur тоже может быть null

Тогда сумма salary + bonus может стать null, и доход посчитается неправильно.

💡 Можно записать и чуть подробнее через CTE:

with prepared as ( select department_id, employee_id, salary_rur, bonus_rur, case when salary_rur is not null or bonus_rur is not null then ifNull(salary_rur, 0) + ifNull(bonus_rur, 0) else null end as income from employee_salary )

select department_id, count() as employees_cnt, countIf(salary_rur is not null) as employees_with_salary_cnt, avg(income) as avg_income from prepared group by department_id order by department_id

Этот вариант иногда удобнее, потому что бизнес-логику income видно отдельно.

🎯 Вывод:

Это хорошая задача на: • работу с NULL • условную агрегацию • аккуратный расчет среднего

Главная мысль: не каждый null нужно превращать в 0 бездумно. Иногда null нужно исключить из расчета, а иногда — заменить на 0 внутри формулы.

Именно на этом чаще всего и ломаются метрики 👌