Босс П3. Почему опаздывает Гонец

Это третий босс курса. Новых конструкций почти нет: всё нужное ты прошёл в блоке E и в блоке D. Новое в том, что время здесь врёт сразу тремя способами, а ответ нужен в виде одной рекомендации. Ставка тоже выше, чем в прошлых боссах: по этой цифре Отто передвинет живых людей, и если она неверна, курьеры выйдут не в те часы, а клиенты будут ждать дальше.

Ким не глупит: он повторяет запрос, который видел у разработчиков, и запрос честно делает то, что написано. Ловушку из урока про пояса он уже встречал, но тогда она была в одном отчёте, а здесь сидит в главной метрике компании.

Фазы те же, что у первого босса. Разведка: понять, что не так с данными. Бой: посчитать долю опозданий в разных разрезах. Трофей: вывод и одна рекомендация для Отто.

Договоримся об определении. Заказ опоздал, если его доставили позже обещанного: местное время доставки больше promised_at. Доля опозданий это опоздавшие заказы, делённые на все доставки. Такой показатель называют SLA.

Фаза 1. Разведка

Начнём с цифры разработчиков. Их дашборд сравнивает момент доставки с обещанным напрямую:

SELECT round(100.0 * avg((delivered_at > promised_at)::int), 1) AS naive_late_pct
FROM orders
WHERE status = 'delivered' AND delivered_at IS NOT NULL;

 naive_late_pct
----------------
            1.2

Вот откуда 1%. Ты уже видишь подвох из урока про пояса: promised_at записан сайтом в местном времени Екатеринбурга без пояса, а delivered_at приложением курьера в UTC. PostgreSQL принимает местное время за UTC, и каждая доставка выглядит на пять часов раньше. Проверим это и заодно поищем битые записи:

SELECT count(*) FILTER (WHERE delivered_at < created_at) AS naive_before,
       count(*) FILTER (WHERE status = 'delivered' AND delivered_at IS NULL) AS lost_time,
       count(*) FILTER (WHERE status = 'cancelled' AND delivered_at IS NOT NULL) AS ghosts,
       count(*) FILTER (WHERE delivered_at < picked_up_at) AS clock_bugs
FROM orders;

 naive_before | lost_time | ghosts | clock_bugs
--------------+-----------+--------+------------
        38720 |        29 |     17 |         24

• naive_before: 38 720 заказов «доставлены раньше, чем созданы». Сломаны не данные, а сравнение. Почти все доставки укладываются в пять часов, вот они и попадают в ловушку.
• lost_time: 29 заказов со статусом «доставлен», но без времени доставки. Опоздали они или нет, узнать нельзя.
• ghosts: 17 отменённых заказов с временем доставки. Статус потерялся по дороге.
• clock_bugs: 24 доставки раньше забора. Сбой часов в телефоне курьера: оба поля в UTC, так что пояс тут ни при чём.

Отсюда определение чистой доставки на весь отчёт: status = 'delivered' и delivered_at >= picked_up_at. Второе условие отсекает и пустое время доставки, и сбой часов. Сравнение делаем в местном времени: delivered_at AT TIME ZONE 'Asia/Yekaterinburg' > promised_at.