Оптимизация зон доставки: ETA vs реальное время

Условие задания

**Контекст:** Сервис доставки еды предсказывает ETA (Estimated Time of Arrival) для каждого заказа. Нужно проанализировать точность предсказаний по зонам доставки и выявить зоны с систематическим занижением или завышением ETA.

**Данные:**

[см. код в задании]

**Задание:**
1. Для каждой зоны доставки за последние 30 дней рассчитайте: количество доставок, средний ETA, среднее реальное время, среднее отклонение (actual - estimated)
2. Рассчитайте процент заказов, доставленных позже ETA (actual > estimated)
3. Добавьте медиану отклонения и 90-й перцентиль реального времени
4. Пометьте зоны, где средний actual > estimated + 5 минут, как 'underestimated'
5. Отсортируйте по среднему отклонению по убыванию

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE delivery_zones (
    id SERIAL PRIMARY KEY,
    zone_name VARCHAR(100) NOT NULL,       -- 'Центр', 'Юг', 'Север', ...
    avg_distance_km NUMERIC(5,1) NOT NULL
);

CREATE TABLE deliveries (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    zone_id INT REFERENCES delivery_zones(id),
    courier_id INT NOT NULL,
    estimated_minutes INT NOT NULL,         -- предсказанный ETA
    actual_minutes INT,                     -- реальное время (NULL если не доставлен)
    created_at TIMESTAMP NOT NULL,
    status VARCHAR(30) NOT NULL             -- 'delivered', 'cancelled', 'in_progress'
);

Темы

CTE PERCENTILE_CONT отклонения зоны

Подсказки

• Отклонение = actual_minutes - estimated_minutes; положительное = опоздание
• PERCENTILE_CONT(0.5) для медианы, PERCENTILE_CONT(0.9) для 90-го перцентиля
• Процент опозданий: 100.0 * COUNT(*) FILTER (WHERE actual_minutes > estimated_minutes) / COUNT(*)

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Оптимизация зон доставки: ETA vs реальное время"?

Это задание для уровня Middle. Для middle-аналитиков с опытом 1-3 года, требует уверенного владения темой и понимания edge cases.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: CTE, PERCENTILE_CONT, отклонения, зоны.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 15-30 минут — оцениваются подход, корректность, обработка edge cases. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания