Ранг пользователей по тратам внутри города
// Условие
Для каждого пользователя посчитайте общую сумму завершённых заказов и присвойте ранг внутри его города (по убыванию суммы). Выведите city, user_id, total_spent и city_rank. Только пользователи с рангом ≤ 3 (топ-3 в каждом городе). Отсортируйте по city, затем по city_rank.
// Таблицы
user_id | INT |
registration_date | DATE |
acquisition_channel | VARCHAR(20) |
platform | VARCHAR(10) |
city | VARCHAR(50) |
is_premium | BOOLEAN |
order_id | INT |
user_id | INT |
order_date | TIMESTAMP |
amount | NUMERIC(10,2) |
status | VARCHAR(20) |
delivery_days | INT |
promo_code | VARCHAR(20) |
// Что тренирует
// Решение
Подсказка
RANK() или ROW_NUMBER() OVER (PARTITION BY city ORDER BY total_spent DESC). Фильтрация по рангу — через CTE или подзапрос, нельзя в WHERE напрямую.
Сначала попробуй сам → Показать решение
WITH user_spending AS (
SELECT
u.city,
u.user_id,
SUM(o.amount) AS total_spent,
ROW_NUMBER() OVER (
PARTITION BY u.city
ORDER BY SUM(o.amount) DESC
) AS city_rank
FROM sandbox.users u
JOIN sandbox.orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.city, u.user_id
)
SELECT city, user_id, total_spent, city_rank
FROM user_spending
WHERE city_rank <= 3
ORDER BY city, city_rank;Паттерн «топ-N внутри группы» — один из самых частых на собеседовании. ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) нумерует строки внутри каждого города. Нельзя фильтровать по оконной функции напрямую в WHERE — нужна обёртка через CTE или подзапрос. RANK() тоже допустим, но при одинаковых суммах даст одинаковые ранги и может вернуть больше 3 строк.