Десять задач. Решай по одной. Не торопись.

Задача 1: выручка по странам

Менеджмент хочет понять, какие географии приносят больше денег. Посчитай общую выручку по странам клиентов: сумму amount_cents из payments для всех заказов, сгруппированную по customers.country. Сортировка по выручке убыванию. Выведи country, total_cents.

Подсказка: тебе нужно три JOIN’а — customers → orders → payments — и GROUP BY country с SUM.

Наше решение:

SELECT c.country, SUM(p.amount_cents) AS total_cents
FROM customers c
JOIN orders o   ON o.customer_id = c.id
JOIN payments p ON p.order_id    = o.id
GROUP BY c.country
ORDER BY total_cents DESC;

Цепочка: σ нет (берём всех клиентов и все платежи) → ⋈ ⋈ → π с GROUP BY → ORDER. Если ты написал тройной JOIN правильно — задача решена. Распространённая ошибка: забыть JOIN с payments и использовать SUM(o.id) или подобную нелепицу. Без payments мы не знаем суммы.

Задача 2: топ-3 товара по выручке

Найди три самых прибыльных товара — сумма qty * unit_price_cents из order_items. Выведи sku, name, revenue_cents. Сортировка по выручке убыванию.

Наше решение:

SELECT p.sku, p.name, SUM(oi.qty * oi.unit_price_cents) AS revenue_cents
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.sku, p.name
ORDER BY revenue_cents DESC
LIMIT 3;

Зачем p.id в GROUP BY? Потому что p.id — primary key, и хотя SQL-стандарт требует все non-агрегированные колонки в GROUP BY, PostgreSQL знает, что от PK функционально зависят sku и name, и разрешает их в SELECT. Если бы мы группировали только по p.name, и были бы два разных товара с одинаковым именем — мы их склеили бы. PK страхует.

Задача 3: клиенты без заказов

Найди клиентов, которые никогда не оформляли заказ (нет ни одной строки в orders с их customer_id). Выведи email, signup_date. Это базовая anti-join задача.

Альтернативы: LEFT JOIN orders ... WHERE orders.id IS NULL или EXCEPT. Все три варианта эквивалентны. В большинстве СУБД NOT EXISTS чуть лучше оптимизируется на больших данных. Запусти и убедись, что результат тот же, какой бы способ ты ни выбрал.

Задача 4: топ-1 товар в каждой категории

В каждой категории найди один товар с самой высокой ценой. Выведи category_id, category_name, top_product_sku, top_price_cents.

Подсказка: тут нужны оконные функции. Используй ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price_cents DESC).

Самая частая ошибка — использовать MAX(price_cents) в подзапросе и потом JOIN’ить обратно. Это медленнее (два прохода по таблице) и теряет связь с конкретным sku. Window function — правильный инструмент для top-N per group.

Задача 5: cohort retention за два месяца

Для клиентов, которые зарегистрировались в январе 2025, посчитай: сколько из них сделали хотя бы один заказ в следующие два месяца (февраль или март 2025). Выведи cohort_size (всего регистраций в январе), retained (с хотя бы одним заказом в фев-мар), retention_pct.

Несколько важных вещей:

• Когорту изолировали в CTE — чтобы её число использовать дважды.
• NULLIF((SELECT COUNT(*) FROM jan_cohort), 0) защищает от деления на ноль, если когорта пуста.
• Дата < '2025-04-01' (а не <= '2025-03-31') — стандартная привычка для интервалов; точная и работает с любым типом дата/время.

Задача 6: путь от категории до её корня

Категории образуют дерево (categories.parent_id). Дано: category_id = 4 («Ноутбуки»). Покажи весь путь от этой категории до корня дерева: id, name, level (0 для самой ноутбук-категории, 1 для родителя, и т.д.).

Подсказка: рекурсивный CTE.

Anchor — стартовая категория. Recursive — берём родителя на каждом шаге, пока он есть (когда parent_id IS NULL — следующий шаг не находит совпадений и рекурсия останавливается).

Задача 7: первый и последний заказ каждого клиента

Для каждого клиента, у которого есть хоть один заказ, выведи customer_id, first_order_at, last_order_at, total_orders.

Прямой GROUP BY с тремя агрегатами — самое читаемое решение. Альтернатива через window functions была бы избыточной: GROUP BY даёт всю нужную информацию в одной агрегации.

Задача 8: дельта между соседними платежами клиента

Для клиента id = 1 выведи все его платежи в порядке времени, и сколько дней прошло с предыдущего платежа (для первого — NULL).

LAG — окно, смотрящее на одну строку назад. Если предыдущей строки нет (первая в окне) — возвращает NULL. EXTRACT(DAY FROM interval) достаёт целое число дней из интервала.

Задача 9: ABC-классификация товаров

Для каждого товара посчитай его долю в общей выручке, и присвой класс:

• A — топ 70% совокупной выручки (накопительная)
• B — следующие 20%
• C — оставшиеся 10%

Выведи sku, revenue_cents, cum_share_pct, abc_class.

Это «boss-задача» этого capstone. Здесь сходятся: агрегация (SUM по товару), оконные функции с running total, оконные без PARTITION для общей суммы, CASE для классификации, ROUND для красоты. Если ты решил её сам без подглядывания — ты уверенный middle.

Задача 10: что выберет оптимизатор

Дана таблица orders. Выполни EXPLAIN на двух запросах:

1. SELECT * FROM orders WHERE status = 'paid';
2. SELECT * FROM orders WHERE status = 'paid' AND customer_id = 1;

Какой план выберет PostgreSQL сейчас (без индексов)? А что изменится, если сначала создать индекс CREATE INDEX ON orders(status)?

На крошечной таблице (20 строк) PostgreSQL всё равно может выбрать Seq Scan, даже с индексом — потому что прочитать всю таблицу с одной страницы дешевле, чем дёргать индекс. Это нормально и правильно. Индексы окупаются на больших данных. На реальной таблице в миллион строк план будет совсем другой.