Админ смотрит на ракеты на Байконуре 🚀, поэтому сегодня у нас лайт-режим.
Но совсем без тренировки бросить вас, конечно же, не могу 🥰
Сегодня у нас SQL, и моя любимая задачка, чтобы готовить кандидатов к SQL-лайфкодингу. И снова Умскул 🥁
Таблица `orders` (order_id, user_id, program_id, order_date, buy_date, state, order_sum).
Таблица `programs` (id, name, type, direction).
Вывести ТОП-5 программ по количеству заказов за текущий месяц.
Любимая эта задача у меня потому, что она генерит очень много типовых ошибок. Причем у всех.
Причем большинство из которых появляются из-за того что вы боитесь или не умеете задавать вопросы. И лезете сразу решать 😋
🚨 ВАЖНО: Первым делом - разбираем условие
➡️ Что такое ТОП-5 программ?
Мы здесь заходим в степь RANK(), DENSE_RANK() и ситуаций двойной сортировки. Последнее = "если количество заказов равно, бери программу с наименьшим id" (второй раз сортируем по id aka двойная сортировка).
Когда вы видите любые "ТОП-Х", сразу спрашивайте, что от вас хотят. Допустим, вам сказали DENSE_RANK() - идем дальше 💪
➡️ Как определить дату заказа?
После этого внимательно смотрим на поля. У нас есть order_date и buy_date. Вообще довольно интуитивно взять order_date, но правильно было бы спросить это у проверяющего. Допустим, order_date ✌️
➡️ А что если null'ы?
Интуитивно заказы без программ скорее не появятся (будто бы баг), но вот программы без заказов (aka курсы, которые пока никто не купил) - запросто. И этот кейс надо предусмотреть.
Если в топ попадают программы, у которых ноль заказов, их нужно выкинуть или вывести? Это тоже нужно спросить. Допустим, вывести 😎
➡️ Дополнительно
Если вы не очень внимательный, то в такого типа задачах вас могут пытаться подловить еще на двух вещах
1️⃣ Есть ли дубли?
В условии нет никакой инфы о том, как строится табличка orders. Вас могут интересовать две вещи:
• Может ли пользователь в одном заказе купить две программы?
• Может ли одна программа быть оплачена двумя заказами?
2️⃣ Что такое "количество заказов"?
Ну то есть может ли тут потенциально подразумеваться какой-то фильтр а-ля state='success'. Конкретно тут риска нет, но бывают коварные формулировки.
😎 Мы готовы писать код!
WITH monthly_orders AS (
SELECT
p.id AS program_id,
p.name AS program_name,
COUNT(o.order_id) AS orders_cnt
FROM programs p
LEFT JOIN orders o -- именно LEFT, тк выводим нулевые программы
ON o.program_id = p.id
AND o.order_date >= DATE_TRUNC('month', CURRENT_DATE)
AND o.order_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY
p.id,
p.name
),
ranked AS (
SELECT
program_id,
program_name,
orders_cnt,
DENSE_RANK() OVER (ORDER BY orders_cnt DESC) AS rnk -- Использует DENSE_RANK()
FROM monthly_orders
)
SELECT
program_id,
program_name,
orders_cnt
FROM ranked
WHERE rnk <= 5
ORDER BY orders_cnt DESC
Я тут пользуюсь DATE_TRUNC('month', CURRENT_DATE), но есть еще более надежный вариант, который я люблю 😉 Это вот этот:
MONTH(CURRENT_DATE) = MONTH(order_date) AND YEAR(CURRENT_DATE) = YEAR(order_date)
В зависимости от БД может быть еще так:
EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM order_date)
AND EXTRACT(YEAR FROM CURRENT_DATE) = EXTRACT(YEAR FROM order_date)
Как говорится, дешево сердито. Но работает.
————
Пожелайте мне удачного запуска !! 🚀🔥🚀🔥🚀
И признавайтесь, если смогла вас где-то подловить 👦
Предыдущие разборы:
• Разбор задачек с собеседований vol. 4
• Разбор задачек с собеседований vol. 5
• Разбор задачек с собеседований vol. 6