SQL Запросы для Northwind (обновленные)

🟢 Уровень 1: Базовый SQL

Вопрос 1: Фильтрация и сортировка

Задача: Вывести топ-5 клиентов (company_name), у которых наибольшее количество заказов в 1997 году.

Вопрос 2: Агрегация с HAVING

Задача: Найти страны, в которых зарегистрировано более 3 клиентов.

Вопрос 3: JOIN и вычисляемые поля

Задача: Вывести список заказов (order_id, order_date) с суммой заказа (unit_price * quantity * (1 - discount)).

🟡 Уровень 2: Средний уровень

Вопрос 4: Подзапросы и NOT EXISTS

Задача: Найти клиентов, которые никогда не делали заказов.

Вопрос 5: Оконные функции — RANK() и DENSE_RANK()

Задача: Проранжировать продукты по количеству продаж в каждой категории.

Вопрос 6: LAG/LEAD — анализ изменений

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

Вопрос 7: CASE и сегментация

Задача: Классифицировать сотрудников по возрастным группам (используя birth_date).

Вопрос 8: CTE и сложная агрегация

Задача: Найти топ-3 продукта по выручке в каждой категории.

Вопрос 9: Скользящее среднее (Moving Average)

Задача: Рассчитать скользящее среднее выручки за 3 месяца для 1997 года.

🔴 Уровень 3: Продвинутый уровень

Вопрос 10: Рекурсивный CTE (иерархия сотрудников)

Задача: Показать иерархию подчинения сотрудников (employee_id, reports_to).

Вопрос 11: CROSS TAB / PIVOT — продажи по категориям

Задача: Создать отчет по продажам за 1997 год, где строки — месяцы, столбцы — категории продуктов.

Вариант с CASE (кросс-таблица):

Вопрос 12: Анализ повторных покупок

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

Вопрос 13: Cohort Analysis — анализ удержания

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

Вопрос 14: Товары, которые часто покупают вместе

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

Вопрос 15: Процент от общего (оконные функции)

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

Вопрос 16: Сравнение с предыдущим периодом (YoY)

Задача: Сравнить выручку по месяцам текущего и предыдущего года.

Вопрос 17: Анализ по поставщикам

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

Вопрос 18: Customer Lifetime Value (LTV)

Задача: Рассчитать LTV для каждого клиента.

test

📝 Бонус: Вопросы на оптимизацию

Вопрос 19: Оптимизация запроса

Проблемный запрос:

SELECT * FROM order_details od
INNER JOIN orders o ON od.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA'
  AND od.unit_price > 50
  AND o.order_date BETWEEN '1997-01-01' AND '1997-12-31';

Какие индексы вы создадите?

Все запросы адаптированы для PostgreSQL (используется синтаксис EXTRACT, INTERVAL, ::varchar, AGE()). Для MySQL потребуются небольшие корректировки (замена EXTRACT на YEAR()/MONTH(), DATE_ADD, LIMIT и т.д.).