SQL для логистики и доставки
DATE_TRUNC('week', ts) в PostgreSQL (где ts имеет тип timestamp)?Зачем это знать
Сервисы доставки — Яндекс Еда, Самокат, Ozon Fresh, СДЭК, СберМаркет — держат большие команды аналитиков, потому что операции здесь целиком завязаны на данные. Специфика домена в том, что почти любая метрика чувствительна ко времени: клиент ждёт заказ здесь и сейчас, а не «в среднем за месяц». Из-за этого центральная тема — соблюдение SLA (обещанного срока доставки): нарушил обещание — потерял клиента, а иногда и деньги на компенсации.
Поэтому на собесах в доставке и логистике SQL-задачи почти всегда доменные: посчитать время доставки и его хвост, утилизацию курьеров, долю заказов в срок, найти узкое место в цепочке. Ниже — типичные таблицы и готовые запросы под эти вопросы.
Основные таблицы
Обычно вам дают примерно такую модель: заказы, курьеры, доставки и трек-точки маршрута.
-- orders: заказы
(id, user_id, placed_at, delivered_at, status)
-- couriers: курьеры
(id, assigned_at, vehicle_type)
-- deliveries: факт доставки заказа курьером
(order_id, courier_id, picked_up_at, delivered_at, distance_km)
-- routes: точки маршрута (гео-трек)
(delivery_id, lat, lon, TIMESTAMP)Метрики времени
Среднее время доставки
Базовая метрика — сколько в среднем проходит от оформления до вручения. EXTRACT(EPOCH FROM ...) даёт разницу в секундах, делим на 60 для минут:
SELECT
AVG(EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 60) AS avg_minutes
FROM orders
WHERE status = 'delivered'
AND placed_at >= CURRENT_DATE - 30;P95 времени доставки
Среднее скрывает проблемных клиентов: 95% заказов приезжают за 25 минут, но оставшиеся 5% ждут час — и именно они пишут жалобы. Поэтому в доставке всегда смотрят на хвост распределения через перцентили:
SELECT
PERCENTILE_CONT(0.95) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 60
) AS p95_minutes
FROM orders
WHERE status = 'delivered';Время доставки по часу дня
Чтобы понять, когда сервис проседает, разбиваем время по часам оформления. В пиковые часы (обед, вечер) доставка обычно медленнее из-за нехватки курьеров:
SELECT
EXTRACT(HOUR FROM placed_at) AS hour,
AVG(EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 60) AS avg_min,
COUNT(*) AS orders
FROM orders
WHERE status = 'delivered'
GROUP BY hour
ORDER BY hour;Соблюдение SLA
SLA compliance — доля заказов, доставленных в обещанный срок. Классический вопрос на собесе: «посчитай долю заказов, доставленных быстрее 30 минут». Используем CASE внутри SUM и делим на общее число:
SELECT
SUM(CASE WHEN EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 60 < 30 THEN 1 ELSE 0 END) * 1.0 /
COUNT(*) AS sla_compliance
FROM orders
WHERE status = 'delivered';Умножение на 1.0 нужно, чтобы деление было дробным, а не целочисленным.
Метрики курьеров
Утилизация
Утилизация показывает, какую долю смены курьер реально был занят доставками, а не простаивал. Считаем активные часы (в пути) относительно длины смены:
WITH courier_hours AS (
SELECT
courier_id,
SUM(EXTRACT(EPOCH FROM (delivered_at - picked_up_at)) / 3600) AS active_hours,
shift_hours
FROM deliveries d
JOIN courier_shifts s USING (courier_id, shift_date)
GROUP BY courier_id, shift_hours
)
SELECT
courier_id,
active_hours,
shift_hours,
active_hours / shift_hours AS utilization
FROM courier_hours;Заказов за смену
Ещё один срез продуктивности — число доставок на курьера и средняя дистанция. Помогает сравнивать курьеров и находить недогруженных:
SELECT
courier_id,
COUNT(*) AS deliveries,
AVG(distance_km) AS avg_distance
FROM deliveries
WHERE delivered_at >= CURRENT_DATE - 7
GROUP BY courier_id;Воронка заказа
Воронка показывает, где заказы отваливаются: сколько оформлено, принято, доставлено и отменено. Это первый запрос, который просят на диагностике операций:
SELECT
COUNT(*) AS placed,
SUM(CASE WHEN status != 'cancelled' THEN 1 ELSE 0 END) AS accepted,
SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
WHERE placed_at >= CURRENT_DATE - 1;Географический анализ
Заказы по зоне
Разбивка по зонам доставки показывает, где спрос выше и где сервис медленнее. Часто именно гео-разрез вскрывает проблемные районы:
SELECT
zone_id,
COUNT(*) AS orders,
AVG(delivery_time_min) AS avg_time
FROM orders
JOIN zones USING (zone_id)
WHERE placed_at >= CURRENT_DATE - 30
GROUP BY zone_id
ORDER BY orders DESC;Распределение дистанции
Группировка по дистанции помогает понять структуру заказов: близкие доставки быстрые и дешёвые, дальние съедают время курьера. Раскладываем через CASE на бакеты:
SELECT
CASE
WHEN distance_km < 2 THEN '< 2 km'
WHEN distance_km < 5 THEN '2-5 km'
WHEN distance_km < 10 THEN '5-10 km'
ELSE '> 10 km'
END AS distance_bucket,
COUNT(*) AS deliveries,
AVG(delivery_time_min) AS avg_time
FROM deliveries
GROUP BY 1;Операционные инсайты
Поздние доставки
Заказы, приехавшие позже обещанного времени (promised_at), — прямая причина недовольства. Считаем, на сколько минут опоздали, и сортируем от худших:
SELECT
o.*,
EXTRACT(EPOCH FROM (o.delivered_at - o.promised_at)) / 60 AS late_minutes
FROM orders o
WHERE o.delivered_at > o.promised_at
ORDER BY late_minutes DESC;Узкое место
Чтобы понять, где именно теряется время, раскладываем путь заказа на этапы: приём, сборка, доставка. Средняя длительность каждого этапа показывает, что оптимизировать в первую очередь:
SELECT
AVG(EXTRACT(EPOCH FROM (accepted_at - placed_at)) / 60) AS acceptance_min,
AVG(EXTRACT(EPOCH FROM (picked_up_at - accepted_at)) / 60) AS pickup_min,
AVG(EXTRACT(EPOCH FROM (delivered_at - picked_up_at)) / 60) AS transit_min
FROM orders
WHERE status = 'delivered';Смотрим, какой этап самый долгий, и оптимизируем именно его — а не весь процесс наугад.
Фрод и качество
Анализ отмен
Отмены бьют по выручке и по клиентскому опыту. Группировка по причине отмены показывает, что чаще всего идёт не так, и сколько курьеров в этом замешано:
SELECT
cancellation_reason,
COUNT(*) AS count,
COUNT(DISTINCT courier_id) AS couriers_involved
FROM cancelled_orders
GROUP BY 1
ORDER BY count DESC;Аномалии по курьерам
Отдельная задача — найти курьеров с подозрительно высокой долей отмен: это может быть и фрод, и проблема с человеком или зоной. Отбираем тех, у кого доля отмен выше 10%, через HAVING:
SELECT
courier_id,
COUNT(*) AS deliveries,
SUM(CASE WHEN status = 'cancelled_by_courier' THEN 1 ELSE 0 END) AS cancellations,
SUM(CASE WHEN status = 'cancelled_by_courier' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS cancel_rate
FROM deliveries
WHERE delivered_at >= CURRENT_DATE - 7
GROUP BY courier_id
HAVING cancel_rate > 0.10
ORDER BY cancel_rate DESC;Погода и внешние факторы
Корреляция погоды с временем доставки
На время доставки влияют внешние факторы, которые не видны в самих заказах. Если подтянуть погодные данные из внешнего API, видно, что дождь и снег стабильно замедляют доставку — а значит, под непогоду стоит заранее выводить больше курьеров:
SELECT
w.weather,
AVG(d.delivery_time_min) AS avg_time,
COUNT(*) AS orders
FROM deliveries d
JOIN weather w ON DATE(d.delivered_at) = w.DATE
GROUP BY 1;Пиковая нагрузка
Дневные и недельные паттерны
Чтобы планировать смены курьеров и мощности, нужно знать, когда приходит спрос. Разрез по дню недели и часу вскрывает регулярные пики — вечер пятницы, обед в будни:
SELECT
EXTRACT(DOW FROM placed_at) AS day_of_week,
EXTRACT(HOUR FROM placed_at) AS hour,
COUNT(*) AS orders
FROM orders
WHERE placed_at >= CURRENT_DATE - 30
GROUP BY 1, 2;Точность ETA
ETA (estimated time of arrival) — обещанное клиенту время. Его точность напрямую влияет на доверие: систематическая ошибка раздражает даже при быстрой доставке. Считаем среднюю ошибку прогноза и её хвост:
SELECT
AVG(EXTRACT(EPOCH FROM (actual_delivery_at - estimated_delivery_at)) / 60) AS avg_error_min,
PERCENTILE_CONT(0.95) WITHIN GROUP (
ORDER BY ABS(EXTRACT(EPOCH FROM (actual_delivery_at - estimated_delivery_at)) / 60)
) AS p95_error
FROM deliveries;Чем ближе средняя ошибка к нулю, тем точнее прогноз. Знак ошибки тоже важен: стабильно приезжать раньше — не так болезненно, как стабильно опаздывать.
Возвраты и рефанды
Return rate — доля заказов, по которым был возврат или рефанд. В доставке целевое значение обычно держат ниже 2%: выше — сигнал проблем с качеством или сборкой:
SELECT
COUNT(*) * 1.0 /
(SELECT COUNT(*) FROM orders WHERE placed_at BETWEEN '2026-01-01' AND '2026-02-01') AS return_rate
FROM returns
WHERE created_at BETWEEN '2026-01-01' AND '2026-02-01';На собесе
«Разберите ключевые метрики доставки». Хороший ответ идёт по блокам: метрики времени (среднее, P95, доля в срок по SLA), продуктивность курьеров (утилизация, заказов за смену), гео- и пиковые паттерны, качество (отмены, возвраты, точность ETA). И обязательно связывайте цифры с бизнесом: скорость и предсказуемость доставки напрямую влияют на удержание клиентов и юнит-экономику.
«Как бы вы оптимизировали время сборки заказа?» Начните с диагностики: разложите путь заказа на этапы и найдите самый долгий (запрос про узкое место выше). Дальше — разрезы: какие магазины или дарксторы медленнее, в какие часы, упирается ли всё в инфраструктуру или в поведение курьеров. Оптимизировать нужно узкое место, а не процесс целиком.
Компании
Яндекс Еда / Лавка. Аналитика фуд-доставки и быстрых заказов из даркстора: огромный поток заказов и жёсткие требования к скорости.
Самокат. Экспресс-доставка продуктов из собственных дарксторов, где ключевая метрика — время от заказа до двери.
Ozon Fresh. Онлайн-продукты в связке с маркетплейсом: сборка, слоты доставки, свежесть товара.
СДЭК. Междугородняя и международная логистика посылок — здесь на первый план выходят маршруты, сортировочные центры и сроки между городами.
СберМаркет. Мультимерчант-платформа: сборка заказов в чужих магазинах добавляет сложности в аналитику качества.
Масштаб и специфика у всех разные, но базовый набор SQL-метрик — время, SLA, курьеры, гео — переносится между ними почти без изменений.
Связанные темы
FAQ
Нужен ли для доставки real-time дашборд?
Да. Операционным командам нужны живые метрики — сколько заказов сейчас в работе, где растёт время доставки, хватает ли курьеров в зоне. Аналитика «за вчера» здесь помогает планировать, но не тушить текущие пожары, поэтому оперативные метрики считают в near-real-time.
Почему в доставке так часто используют ClickHouse?
Из-за объёма событий. Трек-точки маршрутов, статусы заказов, клики в приложении генерируют миллиарды строк, и колоночная ClickHouse на таких данных считает агрегаты в разы быстрее классического PostgreSQL. Транзакционные данные при этом обычно живут в PostgreSQL, а в ClickHouse уезжает аналитический слой.
Почему в доставке смотрят на P95, а не только на среднее?
Потому что клиентский опыт определяет хвост, а не среднее. Среднее время может быть отличным, пока 5% клиентов ждут вдвое дольше и пишут жалобы. P95 и P99 показывают именно этих недовольных, поэтому SLA почти всегда формулируют через перцентиль, а не через average.
Как считать утилизацию курьера корректно?
Нужно чётко определить, что считать «активным» временем: только время в пути с заказом или ещё ожидание на точке и путь до ресторана. От определения сильно зависит цифра. На собесе стоит проговорить границу и считать активные часы относительно фактической длины смены, а не календарного дня.
Чем ETA отличается от фактического времени доставки?
ETA — это прогноз, который показывают клиенту в момент заказа, а фактическое время — то, что получилось на деле. Метрика качества здесь не сама скорость, а точность прогноза: разница между обещанным и фактическим временем. Систематическое отклонение (всегда опаздываем на 5 минут) вреднее случайного разброса, потому что подрывает доверие к сервису.
Тренируйте SQL для аналитики — откройте тренажёр с 1500+ вопросами для собесов.