GROUP BY и агрегация: задачи для найма

GROUP BY — сердце аналитических запросов. Без уверенного владения агрегацией аналитик не сможет построить ни один бизнес-отчёт. Эта подборка задач поможет проверить кандидатов на всех уровнях.

Почему агрегация важнее, чем кажется

Казалось бы, GROUP BY — базовая тема. Но в реальности именно здесь часто встречаются ошибки:

• Неверное применение HAVING вместо WHERE (и наоборот)
• Не понимание, почему нельзя выбрать поле, которого нет в GROUP BY
• Проблемы с NULL в агрегатных функциях
• Неверное поведение COUNT(*) vs COUNT(column)

Задача 1: Базовая агрегация (Junior)

-- Таблица: sales(id, seller_id, product_id, quantity, price, sale_date)
-- Задача: посчитайте выручку и количество продаж по каждому продавцу

SELECT
    seller_id,
    COUNT(*)                    AS sales_count,
    SUM(quantity * price)       AS total_revenue
FROM sales
GROUP BY seller_id
ORDER BY total_revenue DESC;

Вопрос для обсуждения: «Что изменится, если написать COUNT(id) вместо COUNT(*)?» В этом случае — ничего (id не NULL). Но если бы поле могло быть NULL, COUNT(column) не считал бы NULL-значения.

Задача 2: HAVING vs WHERE (Junior+)

-- Задача: найдите продавцов, сделавших более 10 продаж
-- И с суммарной выручкой больше 100 000 руб., только за 2026 год

-- Неверный подход (частая ошибка):
SELECT seller_id, COUNT(*), SUM(quantity * price)
FROM sales
WHERE COUNT(*) > 10  -- ОШИБКА: агрегатные функции нельзя в WHERE
GROUP BY seller_id;

-- Верный подход:
SELECT seller_id, COUNT(*) AS cnt, SUM(quantity * price) AS revenue
FROM sales
WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01'
GROUP BY seller_id
HAVING COUNT(*) > 10
   AND SUM(quantity * price) > 100000;

Ключевой вопрос: «Объясните, в каком порядке SQL обрабатывает WHERE и HAVING?» Правильный ответ: WHERE фильтрует строки ДО группировки, HAVING — ПОСЛЕ.

Задача 3: Агрегация с CASE WHEN (Middle)

-- Задача: для каждого региона посчитайте отдельно
-- выручку по каждой категории товаров (сводная таблица)

SELECT
    region,
    SUM(CASE WHEN category = 'Электроника' THEN amount ELSE 0 END) AS electronics,
    SUM(CASE WHEN category = 'Одежда'      THEN amount ELSE 0 END) AS clothing,
    SUM(CASE WHEN category = 'Продукты'    THEN amount ELSE 0 END) AS food,
    SUM(amount)                                                     AS total
FROM sales s
JOIN products p ON p.id = s.product_id
JOIN regions r  ON r.id = s.region_id
GROUP BY region;

Что проверяем: CASE WHEN внутри агрегатной функции — один из самых полезных паттернов для аналитики.

Задача 4: Подсчёт уникальных значений (Middle)

-- Задача: для каждого месяца 2026 года посчитайте:
-- - число уникальных покупателей
-- - число уникальных купленных продуктов
-- - среднее число разных продуктов на покупателя

SELECT
    DATE_TRUNC('month', sale_date)      AS month,
    COUNT(DISTINCT customer_id)         AS unique_customers,
    COUNT(DISTINCT product_id)          AS unique_products,
    ROUND(
        COUNT(DISTINCT product_id)::NUMERIC
        / COUNT(DISTINCT customer_id), 2
    )                                   AS products_per_customer
FROM sales
WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01'
GROUP BY 1
ORDER BY 1;

Что проверяем: COUNT(DISTINCT ...), арифметика с агрегатными функциями, DATE_TRUNC.

Задача 5: Многоуровневая агрегация с ROLLUP (Senior)

-- Задача: выведите выручку с промежуточными итогами
-- по региону и категории (ROLLUP)

SELECT
    COALESCE(region, 'Все регионы')     AS region,
    COALESCE(category, 'Все категории') AS category,
    SUM(amount)                         AS revenue
FROM sales
GROUP BY ROLLUP (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

Зачем спрашивать: ROLLUP используется в реальных отчётах для добавления строк с итогами. Senior должен знать ROLLUP, CUBE и GROUPING SETS.

Задача 6: Агрегация с оконными функциями вместе (Senior)

-- Задача: для каждого месяца покажите выручку и её долю
-- в годовой выручке

SELECT
    DATE_TRUNC('month', sale_date)          AS month,
    SUM(amount)                             AS monthly_revenue,
    SUM(SUM(amount)) OVER (
        PARTITION BY DATE_TRUNC('year', sale_date)
    )                                       AS yearly_revenue,
    ROUND(100.0 * SUM(amount)
               / SUM(SUM(amount)) OVER (
                   PARTITION BY DATE_TRUNC('year', sale_date)
               ), 1)                        AS pct_of_year
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;

Тонкость: SUM(SUM(amount)) OVER (...) — агрегация внутри оконной функции по уже агрегированному значению. Это продвинутый паттерн, который знают далеко не все Senior-кандидаты.

Типичные ошибки при агрегации

Вопрос на понимание

«Что вернёт этот запрос?»

SELECT category, SUM(price)
FROM products
WHERE price > 100
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY SUM(price) DESC
LIMIT 3;

Хороший кандидат без запуска объяснит: из продуктов дороже 100 руб. выбираем категории с более чем 5 товарами, сортируем по суммарной цене, берём топ-3.

Готовьте SQL-аналитиков с нуля до Middle — курс на SQLlab.ru построен вокруг именно таких аналитических задач с реальными датасетами.