Агрегатные функции SQL: задачи для проверки кандидатов

Агрегатные функции — это фундамент аналитического SQL. Без них невозможно посчитать метрики, построить отчёт или ответить на любой бизнес-вопрос вида «сколько», «в среднем», «максимальный». Если кандидат не уверенно работает с агрегациями, он не справится с реальными задачами аналитика.

Эта статья — практический справочник для HR: какие задачи давать, что проверять и как интерпретировать результаты.

Какие агрегатные функции должен знать кандидат

Обязательный минимум

• COUNT(*) — количество строк
• COUNT(column) — количество непустых значений (без NULL)
• SUM(column) — сумма
• AVG(column) — среднее
• MIN(column) / MAX(column) — минимум и максимум

Средний уровень

• COUNT(DISTINCT column) — количество уникальных значений
• Комбинация с GROUP BY и HAVING
• GROUP BY ROLLUP / CUBE (для продвинутых)
• Агрегация с фильтрацией: SUM(CASE WHEN ... THEN ... END)

Продвинутый уровень

• Оконные агрегации: SUM(...) OVER (PARTITION BY ...)
• FILTER clause: COUNT(*) FILTER (WHERE condition)
• Работа с пустыми группами и NULL в агрегациях

Задачи по уровням сложности

Для тестирования используйте таблицу orders:

orders: id, user_id, product_id, amount, status, created_at

Уровень 1 — Junior

Задача: Посчитайте общее количество заказов и суммарную выручку.

Ожидаемый ответ:

SELECT COUNT(*) AS total_orders, SUM(amount) AS total_revenue
FROM orders;

Что проверяет: базовое использование COUNT и SUM.

Задача: Найдите среднюю сумму заказа по каждому статусу заказа.

Ожидаемый ответ:

SELECT status, AVG(amount) AS avg_amount
FROM orders
GROUP BY status;

Что проверяет: GROUP BY + AVG.

Уровень 2 — Middle

Задача: Найдите топ-5 пользователей по суммарной выручке. Выведите user_id и сумму.

Ожидаемый ответ:

SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 5;

Что проверяет: GROUP BY + ORDER BY + LIMIT, правильный порядок операторов.

Задача: Найдите месяцы, в которых было сделано больше 100 заказов.

Ожидаемый ответ:

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY 1
HAVING COUNT(*) > 100
ORDER BY 1;

Что проверяет: HAVING vs WHERE, работа с датами.

Задача: Посчитайте количество уникальных пользователей, сделавших хотя бы один заказ за последние 30 дней.

Ожидаемый ответ:

SELECT COUNT(DISTINCT user_id) AS active_users
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';

Что проверяет: COUNT DISTINCT, работа с датами.

Уровень 3 — Middle+

Задача: Посчитайте выручку по каждому месяцу отдельно для заказов со статусом completed и cancelled.

Ожидаемый ответ:

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
  SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_revenue
FROM orders
GROUP BY 1
ORDER BY 1;

Что проверяет: условная агрегация с CASE WHEN — очень распространённый паттерн в реальной аналитике.

Задача: Найдите пользователей, у которых средняя сумма заказа выше общей средней по всей таблице.

Ожидаемый ответ:

SELECT user_id, AVG(amount) AS user_avg
FROM orders
GROUP BY user_id
HAVING AVG(amount) > (SELECT AVG(amount) FROM orders);

Что проверяет: HAVING с подзапросом.

Типичные ошибки кандидатов

WHERE вместо HAVING

-- Неправильно:
SELECT user_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 1000  -- ошибка!
GROUP BY user_id;

-- Правильно:
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

Это классическая ошибка, которая говорит о пробеле в базовых знаниях.

Игнорирование NULL в AVG Кандидат не понимает, что AVG автоматически игнорирует NULL-значения, и пишет лишнюю фильтрацию.

Неправильный порядок GROUP BY и WHERE Некоторые кандидаты путают порядок выполнения операторов.

Что спрашивать после теста

После технической части задайте открытые вопросы:

• «Какие агрегатные функции вы использовали чаще всего в своей работе?»
• «Расскажите о задаче, где вам пришлось написать сложный GROUP BY-запрос.»
• «Чем отличается COUNT(*) от COUNT(column)?»

Последний вопрос особенно хорош: COUNT(*) считает все строки включая NULL, COUNT(column) — только непустые. Незнание этого — признак недостаточного опыта.

Как использовать результаты

На sqllab.ru задачи на агрегатные функции доступны в готовых наборах для разных ролей. HR может отправить кандидату ссылку и получить автоматически проверенный результат с указанием, какие задачи решены верно.

Итог

Агрегатные функции — must-have для любого аналитика. Junior должен уверенно работать с COUNT/SUM/AVG и GROUP BY. Middle — с HAVING, COUNT DISTINCT и условной агрегацией через CASE WHEN. Если кандидат путается в разнице между WHERE и HAVING, это сигнал для более глубокой проверки базовых знаний.