Задача из собеседования (VK, intern-аналитик): медиана зарплат по отделам без MEDIAN 📊
Любимый вопрос на собесах: проверяет понимание оконных функций и аккуратность с чётным/нечётным числом строк.

📋 Схема БД
🔹 employees (id, name, department, salary)

🎯 Формулировка
Посчитать медианную зарплату для каждого отдела. Использовать MEDIAN / PERCENTILE_CONT нельзя — нужно реализовать руками.

📊 Пример данных

id name  dept salary
1  Анна  IT   80
2  Борис IT   120
3  Вера  IT   150
4  Глеб  HR   60
5  Дина  HR   90

Ожидаемый результат:

dept median
IT   120    ← центр из 3
HR   75     ← (60+90)/2

Разбор
🧠 Что такое медиана на пальцах
🔹 Сортируем зарплаты по возрастанию
🔹 Если строк нечётно — берём центральную
🔹 Если чётно — среднее двух центральных
Для отдела с зарплатами [40, 50, 70, 100] медиана = (50 + 70) / 2 = 60.

1️⃣ Что нам нужно знать про каждую строку
Чтобы найти «центральную» строку в отделе, нужны две вещи:
🔹 её позиция в отсортированном списке внутри отдела
🔹 общее число строк в отделе
Обе задачи решают оконные функции — ROW_NUMBER() и COUNT(*) с PARTITION BY department.

2️⃣ Как найти центральные позиции
Формула для центра:
🔹 FLOOR((cnt + 1) / 2) — нижняя центральная
🔹 CEIL((cnt + 1) / 2) — верхняя центральная
Магия в том, что для нечётного cnt обе формулы дают одну и ту же строку — это и есть единственная центральная. Для чётного — две соседние, которые мы потом усредним.

Проверим на наших данных:
🔹 IT (cnt=3): FLOOR(4/2)=2, CEIL(4/2)=2 → берём строку №2 → зарплата 120 ✅
🔹 HR (cnt=2): FLOOR(3/2)=1, CEIL(3/2)=2 → строки №1 и №2 → (60+90)/2 = 75 ✅

3️⃣ Решение через оконные функции

WITH ranked AS (
    SELECT 
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY salary
        ) AS rn,
        COUNT(*) OVER (PARTITION BY department) AS cnt
    FROM employees
)
SELECT 
    department,
    AVG(salary * 1.0) AS median_salary
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
GROUP BY department;

CTE ranked внутри каждого отдела нумерует сотрудников от самой низкой зарплаты к самой высокой и параллельно считает общее число сотрудников. Внешний запрос оставляет только «центральные» строки и усредняет их.

На что обратить внимание❗️
🔹 Деление на 2.0, а не на 2 — иначе в некоторых БД получим целочисленное деление и логика для чётных отделов поедет
🔹 AVG(salary * 1.0) — та же история, если зарплаты хранятся в INT, без умножения на 1.0 результат округлится до целого
🔹 COUNT(*) посчитает и NULL-зарплаты как строки. Если такое возможно — используйте COUNT(salary) и заранее отфильтруйте WHERE salary IS NOT NULL, иначе ROW_NUMBER поставит NULL в начало и сломает порядок

🎁 Бонус: если PERCENTILE_CONT всё-таки доступен
В PostgreSQL, Oracle, SQL Server задача решается в одну строку:

SELECT 
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees
GROUP BY department;

Но на собесе обычно просят именно «руками» — чтобы проверить, понимаете ли вы, что происходит под капотом 🔍

🎯 Что запомнить
🔹 Медиана руками = ROW_NUMBER + COUNT в одном CTE
🔹 Центральные позиции — FLOOR и CEIL от (cnt+1)/2
🔹 Делим на 2.0, а не на 2 — иначе целочисленное деление всё сломает
🔹 Если в БД есть PERCENTILE_CONT — используйте его; ручная реализация нужна только для собесов и старых MySQL (до 8.0 там вообще нет оконных функций — придётся через переменные)