Ну что, так как все любят разборы задачек с собесов, то вот вам еще одна!
Нужно вывести разницу в выручке в процентах по каждому региону, сравнивая октябрь 2024 с сентябрем 2024.
📌 Структура таблиц:
region – данные о регионах и городах
- id - первичный ключ
- region_id - id региона
- region_name - название региона
- city_id - id города
- city_name - название города
payments – информация о платежах
- id - первичный ключ
- payment_id - id платежа
- dt - дата и время платежа
- amount - сумма платежа
- city_id - id города
1️⃣ Предварительный запрос. Соединяем таблицы и группируем данные.
Для начала нужно связать таблицы region и payments по полю city_id, чтобы понять, к какому региону относится каждый платёж.
Затем:
Извлекаем год и месяц из даты платежа (dt) с помощью substring(dt, 1, 7) (формат 'YYYY-MM'). Я всегда делаю это с годом на всякий случай, а то вдруг в данных разные года представлены.
Суммируем выручку (amount) по каждому региону и месяцу.
Делаем это все в CTE. По сути, просто предварительный расчет чего-либо.
🔹 CTE (Common Table Expression) — это временный результат запроса, который можно использовать в последующих частях SQL-запроса.
Вот так начинается наш CTE
with monthly_revenue as
. Теперь на основе этой таблички мы сможем делать уже основной запрос со сравнением:
2️⃣Основной запрос. Сравниваем выручку между сентябрём и октябрём
Теперь, когда у нас есть выручка по месяцам, можно рассчитать разницу в процентах между октябрём (2024-10) и сентябрём (2024-09).
Используем:
✅ FILTER – отбирает только нужные строки для агрегации (аналог CASE WHEN).
✅ NULLIF – защищает от деления на ноль (если в сентябре выручки не было).
Тут уже мы во FROM пишем monthly_revenue. То есть вытаскиваем из подзапроса уже данные.
query = f"""
with monthly_revenue as (
select region_id,
region_name,
substring(dt,1,7) as month, -- оставляем только год и месяц
sum(amount) as amount -- суммируем выручку
from region_df r
join payments_df p on p.city_id=r.city_id
group by 1,2,3
)
select region_id,
region_name,
((sum(amount) filter (where month='2024-10') - sum(amount) filter (where month='2024-09'))*1.0/
nullif(sum(amount) filter (where month='2024-09'), 0))*100 as rate
from monthly_revenue
group by 1,2
"""
result = sqldf(query)
💡 Итог:
Используй CTE (WITH) для сложных запросов — это как создание «временных таблиц» для удобства. А подзапросы — когда нужна быстрая вставка логики в WHERE или JOIN.
Оба подхода делают SQL мощнее, но CTE — это чище, понятнее и переиспользуемо ✨
Кто любит CTE - ставьте 🐳, кто за подзапросы - 🦄. Ну а если и то и то любите - 🔥