Условие

Структура данных:

users(id, name, email, created_at)
orders(id, user_id, total_price, created_at)
order_items(id, order_id, product_name, price, quantity)

Реализуйте 4 SQL-запроса (ожидается оптимальное решение, тестовая база — не менее 1 млн строк в каждой таблице):

Общее количество заказов для каждого пользователя, кто сделал более 10 заказов.
Средний размер заказа для каждого пользователя за последний месяц.
Средний размер заказа за каждый месяц текущего года и сравнение со средним за тот же месяц прошлого года.
10 пользователей с наибольшим числом заказов за последний год, и для каждого — средний размер заказа за последний месяц.

Решение

Реализация

Запрос 1. Пользователи с >10 заказов

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS orders_count
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10
ORDER BY orders_count DESC;

Запрос 2. Средний размер заказа за последний месяц

SELECT
  u.id,
  u.name,
  AVG(o.total_price) AS avg_order_price_last_month
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= NOW() - INTERVAL '1 month'
GROUP BY u.id, u.name;

«Последний месяц» = последние 30 дней. Если задача про календарный последний месяц — фильтр другой:

WHERE EXTRACT(YEAR  FROM o.created_at) = EXTRACT(YEAR  FROM NOW() - INTERVAL '1 month')
  AND EXTRACT(MONTH FROM o.created_at) = EXTRACT(MONTH FROM NOW() - INTERVAL '1 month')

Или ещё чище через DATE_TRUNC:

WHERE DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', NOW() - INTERVAL '1 month')

Запрос 3. Помесячное сравнение этого года и прошлого

WITH monthly AS (
  SELECT
    EXTRACT(YEAR  FROM created_at) AS yr,
    EXTRACT(MONTH FROM created_at) AS mo,
    AVG(total_price)                AS avg_order
  FROM orders
  WHERE created_at >= DATE_TRUNC('year', NOW()) - INTERVAL '1 year'
    AND created_at <  DATE_TRUNC('year', NOW()) + INTERVAL '1 year'
  GROUP BY 1, 2
)
SELECT
  c.mo                                                    AS month,
  c.avg_order                                              AS avg_current_year,
  p.avg_order                                              AS avg_previous_year,
  CASE WHEN p.avg_order > 0
       THEN (c.avg_order - p.avg_order) / p.avg_order * 100
       ELSE NULL END                                       AS yoy_change_pct
FROM monthly c
LEFT JOIN monthly p
       ON p.yr = c.yr - 1 AND p.mo = c.mo
WHERE c.yr = EXTRACT(YEAR FROM NOW())
ORDER BY c.mo;

Альтернатива — self-join по (yr, mo) без CTE.

Запрос 4. Топ-10 за год + средний чек за месяц

WITH top10 AS (
  SELECT
    user_id,
    COUNT(*) AS orders_last_year
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '1 year'
  GROUP BY user_id
  ORDER BY COUNT(*) DESC
  LIMIT 10
),
last_month AS (
  SELECT
    user_id,
    AVG(total_price) AS avg_order_last_month
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '1 month'
  GROUP BY user_id
)
SELECT
  u.id,
  u.name,
  t.orders_last_year,
  COALESCE(lm.avg_order_last_month, 0) AS avg_order_last_month
FROM top10 t
JOIN users u ON u.id = t.user_id
LEFT JOIN last_month lm USING (user_id)
ORDER BY t.orders_last_year DESC;

LEFT JOIN — потому что юзер из топ-10 за год мог не сделать заказ за последний месяц.

Оптимизация для больших объёмов (1 млн+)

Индексы

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_created ON orders (created_at);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);  -- compound

Композитный индекс (user_id, created_at) ускоряет:

запрос 1 (GROUP BY user_id с фильтром)
запрос 2 (GROUP BY user_id + WHERE created_at)

Партиционирование

Для очень больших объёмов — partition by month (PARTITION BY RANGE (created_at)). Запрос за «последний месяц» работает только с одной партицией.

Параметризация дат

В проде — заменить NOW() на параметр (для воспроизводимости и тестов):

WHERE created_at >= :date_from AND created_at < :date_to

Запрос 4 — оптимизация

LIMIT 10 после ORDER BY COUNT(*) требует полной агрегации. Нет короткого пути, но композитный индекс (user_id, created_at) помогает.

В проде на ClickHouse — topK(10)(user_id) бы работал.

Анализ / интерпретация

Эти 4 запроса — стандартный аналитический набор: «активные клиенты», «свежий чек», «YoY», «топ + сегмент». Покрывают 80% базовых запросов аналитика на e-commerce данных.

Подводные камни

COUNT(o.id) vs COUNT(*): после JOIN с users — без разницы, но при LEFT JOIN (если бы был) — COUNT(o.id) правильно даёт 0 для юзеров без заказов.
«Последний месяц» — что это? 30 дней или календарный? Уточняйте.
HAVING vs WHERE: фильтрация после агрегации — только в HAVING. WHERE COUNT(*) > 10 не работает.
Часовые пояса. NOW() — UTC на postgres-сервере; для пользовательской отчётности может быть нужен local.
Незакрытые периоды. «Последний месяц» включает сегодняшний неполный день — отчёт может быть нестабильным. Лучше brus фиксированных границ.
AVG(total_price) усредняет по заказам, а не «среднее на пользователя». Если задача «средний чек юзера», всё ок. Если «средний из средних чеков юзеров» — другая агрегация.
Last year для запроса 4. Год календарный или 365 дней? Уточняйте.
LIMIT 10 без ORDER BY детерминизм не гарантирован. Если у нескольких юзеров одинаковое число заказов, результат недетерминирован. Добавляйте ORDER BY orders_last_year DESC, user_id.
LEFT JOIN для last_month в запросе 4: иначе юзер из топ-10, не покупавший за последний месяц, выпадает из результата.

Эталонный ответ

4 запроса через комбинации JOIN + GROUP BY + HAVING + WHERE + LIMIT. Для оптимизации — индексы на (user_id, created_at) и партиционирование по месяцам. Главные тонкости: LEFT JOIN для незаполненных сегментов в запросе 4, DATE_TRUNC для корректного «последнего месяца», CTE с self-join для year-over-year в запросе 3.

Happy Games Studio — SQL: 4 запроса по заказам и пользователям