Условие
Таблицы:
orders(order_id, restaurant_id, user_id, dt, gmv, status)
restaurants(restaurant_id, name, city, cuisine)
Для каждой пары (город, кухня) найдите топ-3 ресторана по GMV за последние 28 дней. Покажите также:
- ранг ресторана за предыдущие 28 дней (для сравнения);
- абсолютное изменение GMV и %;
- стрелочку «вырос / упал / стабильно».
Решение
Запрос
WITH base AS (
SELECT
r.restaurant_id,
r.name,
r.city,
r.cuisine,
CASE
WHEN o.dt >= CURRENT_DATE - INTERVAL '28 day'
AND o.dt < CURRENT_DATE THEN 'now'
WHEN o.dt >= CURRENT_DATE - INTERVAL '56 day'
AND o.dt < CURRENT_DATE - INTERVAL '28 day' THEN 'prev'
END AS period,
o.gmv
FROM orders o
JOIN restaurants r ON r.restaurant_id = o.restaurant_id
WHERE o.status = 'delivered'
AND o.dt >= CURRENT_DATE - INTERVAL '56 day'
),
agg AS (
SELECT restaurant_id, name, city, cuisine, period,
SUM(gmv) AS gmv
FROM base
WHERE period IS NOT NULL
GROUP BY 1, 2, 3, 4, 5
),
ranked AS (
SELECT
city, cuisine, restaurant_id, name,
MAX(CASE WHEN period='now' THEN gmv END) AS gmv_now,
MAX(CASE WHEN period='prev' THEN gmv END) AS gmv_prev
FROM agg
GROUP BY 1, 2, 3, 4
),
final AS (
SELECT
city, cuisine, restaurant_id, name,
gmv_now, gmv_prev,
DENSE_RANK() OVER (PARTITION BY city, cuisine ORDER BY gmv_now DESC NULLS LAST)
AS rank_now,
DENSE_RANK() OVER (PARTITION BY city, cuisine ORDER BY gmv_prev DESC NULLS LAST)
AS rank_prev,
gmv_now - COALESCE(gmv_prev, 0) AS abs_diff,
ROUND( (gmv_now - COALESCE(gmv_prev, 0))
* 100.0 / NULLIF(gmv_prev, 0), 1) AS pct_diff
FROM ranked
)
SELECT
city, cuisine, rank_now, name,
gmv_now, gmv_prev, abs_diff, pct_diff,
rank_prev,
CASE
WHEN gmv_prev IS NULL THEN 'NEW'
WHEN gmv_now > gmv_prev * 1.10 THEN 'UP'
WHEN gmv_now < gmv_prev * 0.90 THEN 'DOWN'
ELSE 'STABLE'
END AS trend
FROM final
WHERE rank_now <= 3
ORDER BY city, cuisine, rank_now;Разбор
base— фильтруем последние 56 дней и метим, к какому из двух 28-дневных окон относится заказ.agg— суммируем GMV по периодам.ranked— pivot: одна строка на ресторан с двумя колонкамиgmv_now/gmv_prev.final— ранги и разности.- Финальный SELECT — топ-3 + стрелочка.
NULL-обработка
- Ресторан был в прошлом периоде, но не в текущем →
gmv_now IS NULL.NULLS LASTставит их в хвост ранга. - Новый ресторан (только current) →
trend = 'NEW'.
Альтернатива: одной CTE через FILTER (Postgres)
SELECT
r.city, r.cuisine, r.restaurant_id, r.name,
SUM(o.gmv) FILTER (WHERE o.dt >= CURRENT_DATE - 28) AS gmv_now,
SUM(o.gmv) FILTER (WHERE o.dt >= CURRENT_DATE - 56
AND o.dt < CURRENT_DATE - 28) AS gmv_prev
FROM restaurants r
LEFT JOIN orders o USING (restaurant_id)
WHERE o.status = 'delivered'
GROUP BY 1, 2, 3, 4;Дальше — то же самое с DENSE_RANK.
Подводные камни
status = 'delivered'в JOIN vsWHERE: для LEFT JOIN — вON, чтобы рестораны без заказов сохранились.DENSE_RANKvsRANK: при равных GMV у двух ресторанов rank_now будут одинаковыми у DENSE_RANK без пропусков; RANK пропустит места.- NULLS FIRST/LAST: default разный в разных СУБД — указывать явно.
CURRENT_DATE - 28: в Postgres даёт DATE; в Snowflake — DATEADD. В кросс-движковом коде использоватьINTERVAL.- Свежий ресторан с одним заказом легко попадёт в топ-3 на маленькой выборке. Добавлять минимальный порог
gmv_now > 50_000илиn_orders > 100. - Часовые пояса:
o.dt::dateзависит от сессионной TZ. Для бизнес-отчёта фиксировать TZ ресторана (Asia/Yekaterinburg для Екб etc). pct_diffдля нулевогоgmv_prev→ деление на 0.NULLIF+COALESCE(..., 'NEW').- Бизнес-смысл «топ-3»: иногда хотят «топ-3 по выручке, при условии, что доставка работает 30 дней» — нужен дополнительный фильтр по
min(dt).
Эталонный ответ
WITH p AS (
SELECT restaurant_id, name, city, cuisine,
SUM(gmv) FILTER (WHERE dt >= CURRENT_DATE - 28) AS gmv_now,
SUM(gmv) FILTER (WHERE dt >= CURRENT_DATE - 56
AND dt < CURRENT_DATE - 28) AS gmv_prev
FROM orders JOIN restaurants USING (restaurant_id)
WHERE status = 'delivered' AND dt >= CURRENT_DATE - 56
GROUP BY 1, 2, 3, 4
)
SELECT *, DENSE_RANK() OVER (PARTITION BY city, cuisine ORDER BY gmv_now DESC) AS rk
FROM p
WHERE DENSE_RANK() OVER (PARTITION BY city, cuisine ORDER BY gmv_now DESC) <= 3
ORDER BY city, cuisine, rk;Ключевые приёмы: FILTER WHERE для conditional aggregation; DENSE_RANK() OVER (PARTITION BY ...) для топа по подгруппе; явный NULLS LAST для NULL gmv_now; NULLIF для безопасного процента изменения.