Условие

Таблицы:

restaurants(restaurant_id, restaurant_name, city)
orders(order_id, restaurant_id, order_date, amount)

Для каждого города найдите топ-3 ресторана по выручке за последний полный месяц.

Решение

WITH last_month AS (
  SELECT
    DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')::DATE AS m_start,
    DATE_TRUNC('month', CURRENT_DATE)::DATE AS m_end
),
rev AS (
  SELECT
    r.city,
    r.restaurant_id, r.restaurant_name,
    SUM(o.amount) AS revenue
  FROM orders o
  JOIN restaurants r ON r.restaurant_id = o.restaurant_id
  JOIN last_month lm
    ON o.order_date >= lm.m_start
   AND o.order_date <  lm.m_end
  GROUP BY r.city, r.restaurant_id, r.restaurant_name
),
ranked AS (
  SELECT
    *,
    DENSE_RANK() OVER (PARTITION BY city ORDER BY revenue DESC) AS rnk
  FROM rev
)
SELECT city, restaurant_name, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY city, revenue DESC;

Деталь — DENSE_RANK vs ROW_NUMBER

Если на 3-м месте ничья (двое ресторанов с одинаковой выручкой) — DENSE_RANK <= 3 включит обоих, ROW_NUMBER <= 3 оставит только одного. Зависит от того, что ожидает бизнес.

Деталь — границы месяца

DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') — начало прошлого месяца. < DATE_TRUNC('month', CURRENT_DATE) — строго меньше начала текущего, то есть включая последний день прошлого. BETWEEN тут опасен из-за 23:59:59 если order_date — TIMESTAMP.

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

BETWEEN start AND end. Если order_date TIMESTAMP, день end обрежется на 00:00:00. Использовать >= start AND < end_next.
Ресторан без заказов. Не появится в выходе — это норм по задаче.
Города в restaurants с пробелами/регистром. 'New York ' ≠ 'New York'. На реальной БД нужен TRIM/LOWER.

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

DENSE_RANK() OVER (PARTITION BY city ORDER BY revenue DESC) <= 3, после фильтра по последнему полному месяцу через DATE_TRUNC + INTERVAL.

Glassdoor (DoorDash) — топ-3 ресторана по выручке в каждом городе