SQL для логистики и доставки

Зачем это знать

Сервисы доставки (Яндекс Еда, Delivery, 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)

Метрики времени

Среднее время доставки

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 времени доставки

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

«Заказы, доставленные за < 30 минут»:

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';

Метрики курьеров

Утилизация

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;

Распределение дистанции

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;

Операционные инсайты

Поздние доставки

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;

Аномалии по курьерам

Курьеры с большим числом отмен, возвратов.

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

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;

Ближе к 0 — хорошая точность.

Возвраты и рефанды

SELECT
    COUNT(*) * 1.0 /
        (SELECT COUNT(*) FROM orders WHERE placed_at BETWEEN ... AND ...) AS return_rate
FROM returns
WHERE created_at BETWEEN ...;

Обычно целевое значение < 2%.

На собесе

«Разберите метрики доставки»:

• Метрики времени (среднее, P95, SLA)
• Утилизация курьеров
• Географические и пиковые паттерны
• Качество (отмены, возвраты)

Бизнес-контекст: важно для удовлетворённости клиентов и прибыльности.

«Оптимизируйте время сборки»:

• Какие магазины медленные?
• В какое время?
• Проблемы инфраструктуры?
• Поведение курьеров?

Компании

Яндекс Еда / Лавка

Аналитика фуд-доставки.

Самокат

Быстрая доставка продуктов.

Ozon Fresh

Онлайн-продукты.

СДЭК

Междугородняя доставка.

Delivery

Мультимерчант-платформа.

Разные масштабы и сложность.

Связанные темы

• Собеседование в маркетплейс
• Яндекс Такси
• СберМаркет

FAQ

Нужен ли real-time дашборд?

Да. Операциям нужны живые метрики.

ClickHouse?

Распространён в платформах доставки — большой объём событий.