StrataScratch (DoorDash) — среднее время доставки по городам, исключая выбросы

Условие

Таблица doordash_deliveries(delivery_id, city, delivery_minutes, order_amount). Посчитайте среднее время доставки по каждому городу, исключив верхние 5% и нижние 5% (по delivery_minutes) — то есть «trimmed mean».

Решение

Подход через PERCENTILE_CONT

WITH bounds AS (
  SELECT
    city,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY delivery_minutes) AS p05,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY delivery_minutes) AS p95
  FROM doordash_deliveries
  GROUP BY city
)
SELECT
  d.city,
  ROUND(AVG(d.delivery_minutes)::NUMERIC, 2) AS avg_delivery_trimmed
FROM doordash_deliveries d
JOIN bounds b ON b.city = d.city
WHERE d.delivery_minutes BETWEEN b.p05 AND b.p95
GROUP BY d.city
ORDER BY d.city;

Подход через NTILE(20) (20 бакетов по 5%)

WITH bucketed AS (
  SELECT
    city, delivery_minutes,
    NTILE(20) OVER (PARTITION BY city ORDER BY delivery_minutes) AS bucket
  FROM doordash_deliveries
)
SELECT city, ROUND(AVG(delivery_minutes)::NUMERIC, 2)
FROM bucketed
WHERE bucket BETWEEN 2 AND 19  -- 1-й = нижние 5%, 20-й = верхние 5%
GROUP BY city;

NTILE чуть менее точен на маленьких группах, но быстрее.

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

1. PERCENTILE_CONT — Postgres/Oracle/SQL Server (2012+). В MySQL появилось только в 8.0. В старых версиях — PERCENT_RANK() + фильтр.
2. «Trimmed mean» vs «winsorized mean». Trimmed — выкидываем 5% хвостов. Winsorized — заменяем хвосты на значения p5/p95. Разные методы, разные числа.
3. Малые города. В городе из 5 доставок — 5% хвоста это «полстроки». PERCENTILE_CONT интерполирует — ок. Но trimmed на 5 наблюдениях смысла не имеет, отдельный фильтр на размер группы.
4. BETWEEN включает границы. Если хотите строго хвосты выкинуть и сами p5/p95 тоже — > и <. Обычно BETWEEN ок.

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

PERCENTILE_CONT(0.05/0.95) WITHIN GROUP (ORDER BY delivery_minutes) по городу → JOIN обратно → AVG между p5 и p95. Либо NTILE(20) и фильтр по средним бакетам.