💡Так как сейчас всем не хватает реальной практики задач с собеседований, ловите ещё один интересный разбор
Сегодня решаем задачу:
Найти топ 5% пользователей с наибольшим количеством заказов по name
🟢Данные:
- таблица orders: order_id, user_id, state, category
- таблица users: user_id, name, other
Создадим табличку orders, обойдемся без колонки category. Она все равно тут не нужна:
orders = pd.DataFrame({
"order_id": list(range(1, 41)),
"user_id": [
5,5,5,5,5,5,5,5,5,5, # 10 заказов (Ева)
2,2,2,2,2,2,2,2, # 8 заказов (Саша)
1,1,1,1,1,1, # 6 заказов (Алиса)
3,3,3,3, # 4 заказа (Лена)
4,4,4, # 3 заказа (Давид)
6,7,8,9,10,11,12,13,14 # по одному заказу
],
"state": ["delivered"] * 40
})и таблицу users (без колонки other, незачем нам тут лишняя инфа):
users = pd.DataFrame({
"user_id": list(range(1, 21)),
"name": [
"Алиса", "Саша", "Лена", "Давид", "Ева",
"Марина", "Игорь", "Оля", "Иван", "Юля",
"София", "Саша", "Олег", "Лера", "Миша",
"Наташа", "Ирина", "Петр", "Лиза", "Рита"
]
})✅ Решение:
query = """
with user_orders as (
select u.user_id,
u.name,
count(o.order_id) as cnt
from users u
left join orders o on u.user_id = o.user_id
group by u.user_id, u.name
),
ranked as (
select uo.*,
row_number() over (order by uo.cnt desc) as rn,
(select count(*) from user_orders) as total_users
from user_orders uo
)
select user_id, name, cnt
from ranked
where rn <= ceil(total_users * 0.05)
"""
result = duckdb.query(query).to_df()
🔸Агрегируем данные: для каждого user_id считаем количество заказов cnt = COUNT(order_id).
🔸Используем LEFT JOIN, чтобы не потерять пользователей без заказов.
🔸Ранжируем пользователей по убыванию cnt. Это удобно делать оконной функцией row_number().
🔸Считаем порог - вычисляем total_users и берём ceil(total_users * 0.05) - количество пользователей, попадающих в верхние 5%.
🔸Отбираем те, у кого rn <= порог.
Почему получается в результате 1 юзер? Потому что:
total_users = 20 человек; 5% от 20 = 20 * 0.05 = 1 Потом применяется ceil(если число не целое)
То есть запрос выбирает только одну строку — первого пользователя по рангу (это Ева).
Если пользователей мало, то 5% - это конечно очень маленькое число.
❗️ Есть еще вариант считать что под "топ-5%" имеют в виду тех, у кого количество заказов входит в верхние 5% по значению, а не по позиции в списке. Тогда нужно считать чей cnt ≥ 95-й перцентиль.
Если интересно - попробуйте посчитать с перцентилем. Я выложу такой вариант расчета на днях.