Самокат стажировка SQL — заказы и клиенты по месяцам и городам за 2021

Условие

Таблицы:

• orders(order_id, warehouse_id, user_id, date, paid_amount, quantity) — заказы.
• warehouses(warehouse_id, name, city, date_open, date_close).

Посчитать количество заказов и количество клиентов в разрезе месяцев за 2021 год — отдельно по компании в целом и по каждому городу.

На выходе: город / "company", месяц, orders_cnt, clients_cnt.

Решение

Подход

Используем GROUP BY GROUPING SETS (или ROLLUP) — стандартный SQL способ получить две агрегации (по городу + общую) одним запросом.

Реализация (PostgreSQL / Greenplum / Vertica)

SELECT
  COALESCE(w.city, 'company')    AS scope,
  DATE_TRUNC('month', o.date)    AS month,
  COUNT(*)                       AS orders_cnt,
  COUNT(DISTINCT o.user_id)      AS clients_cnt
FROM orders o
JOIN warehouses w USING (warehouse_id)
WHERE o.date >= DATE '2021-01-01'
  AND o.date <  DATE '2022-01-01'
GROUP BY GROUPING SETS (
  (w.city, DATE_TRUNC('month', o.date)),
  (DATE_TRUNC('month', o.date))
)
ORDER BY month, scope NULLS FIRST;

GROUPING SETS — два уровня:

• (city, month) — детально.
• (month) — итог по компании.

В строках с итогом city = NULL → подменяем на 'company'.

Альтернатива через UNION ALL

Если СУБД (старый MySQL / SQLite) не поддерживает GROUPING SETS:

SELECT w.city AS scope, DATE_TRUNC('month', o.date) AS month,
       COUNT(*) AS orders_cnt, COUNT(DISTINCT o.user_id) AS clients_cnt
FROM orders o JOIN warehouses w USING (warehouse_id)
WHERE o.date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY w.city, DATE_TRUNC('month', o.date)
 
UNION ALL
 
SELECT 'company' AS scope, DATE_TRUNC('month', o.date) AS month,
       COUNT(*) AS orders_cnt, COUNT(DISTINCT o.user_id) AS clients_cnt
FROM orders o
WHERE o.date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY DATE_TRUNC('month', o.date)
ORDER BY month, scope;

Это менее эффективно — данные сканируются дважды.

Тонкость: «уникальные клиенты»

COUNT(DISTINCT user_id) на уровне (city, month) и на уровне month — разные числа. Сумма уникальных клиентов по городам больше или равна уникальным по компании за месяц (один клиент мог заказать в двух городах). Это нормально и нужно объяснить заказчику.

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

1. «За 2021 год» — это включительно по 31.12. Лучше >= '2021-01-01' AND < '2022-01-01', чем BETWEEN, чтобы не зависеть от типа поля (дата/таймстамп).
2. Открытые/закрытые склады. Если склад закрылся в середине года, заказы по нему всё ещё были — фильтровать по warehouses.date_close не нужно, только по дате заказа.
3. COUNT(DISTINCT user_id) NULL. Если user_id бывает NULL (анонимные?), их COUNT DISTINCT не считает. Уточнить.
4. Сумма != сумма. Сумма по городам клиентов > общего числа клиентов компании — частая «ошибка отчёта» у заказчиков.
5. DATE_TRUNC vs EXTRACT(MONTH...). EXTRACT склеит январь 2020 и январь 2021. DATE_TRUNC сохраняет год.

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

GROUP BY GROUPING SETS ((city, month), (month)) с COALESCE(city, 'company') для удобства, фильтр по году. На UNION-варианте — таблица сканируется дважды, что хуже.