Задачка с собеседования: как найти объявления с растущими ценами? Часто в аналитике возникает задача: есть история изменения цен (снимки/snapshots), и нужно найти объекты, где цена росла слишком быстро. Вот эта именно такая задачка может попасться на собеседовании. Дано три таблицы: listings_df - объявления и их параметры; agents_df - информация об агентах, разместивших объявления; districts - информация о районах 🎯 Что нужно сделать? Для каждого объявления: - Сравнить цену на каждом снимке с ценой на предыдущем снимке. - Посчитать процентное изменение цены. - Найти среднее процентное изменение между последовательными снимками. - Оставить объявления, у которых среднее изменение превышает 0,5%. - Округлить результат до двух знаков и отсортировать по убыванию. Причем важно: учитываем все объявления, даже снятые с публикации. И не нужно искать объявления, у которых цена выросла больше 0,5% хотя бы один раз. Нас интересует именно среднее изменение цены. Разберем как можно решить эту задачу. 🧩 1️⃣ Самое интересное — функция LAG() Чтобы сравнить цену объявления с предыдущим снимком, нам нужно как-то получить предыдущую цену в текущей строке. Для этого в SQL есть оконная функция lag(). Она позволяет посмотреть на значение из предыдущей строки, не объединяя таблицу саму с собой. Представим, что мы хотим для каждой даты узнать цену на предыдущем снимке. WITH prev_amount AS ( SELECT listing_id, snapshot_date, price, LAG(price) OVER (PARTITION BY listing_id ORDER BY snapshot_date ) AS prev FROM listings_df ) 🔹 lag(price) - возьми значение price из предыдущей строки. 🔹 partition by listing_id - раздели данные на группы по объявлениям. Для каждого объявления история цен будет рассчитываться отдельно. 🔹 order by snapshot_date - расположи снимки в хронологическом порядке. Именно так SQL понимает, какая строка является предыдущей. 2️⃣ Считаем средний процент изменения. Когда предыдущая цена у нас есть, можно посчитать изменение. SELECT listing_id, -- Считаем процент и сразу округляем ROUND(AVG(100.0 * (price - prev) / NULLIF(prev, 0)), 2) AS avg_change FROM prev_amount GROUP BY 1 HAVING avg_change > 0.5 ORDER BY 2 desc Использую 100.0, чтобы вычисление выполнялось с дробной частью. 3️⃣Фильтруем и сортируем. Фильтруем уже агрегированные результаты. Здесь нас интересует среднее по объявлению, а не отдельные строки с изменением цены. HAVING avg_change > 0.5 ORDER BY 2 desc Сортируем по второму столбцу - среднему изменению - от большего к меньшему. На мой взгляд, это одни из самых полезных оконных функций для аналитика. С их помощью можно решать задачи на изменение метрик, анализ последовательности событий, поиск пропусков и многое другое. 🗂 В условии были перечислены сразу несколько таблиц: listings, agents, districts и таблица со снимками цен. Но для решения задачи нам нужна только одна! 💡 Маленький лайфхак: не стоит автоматически делать join всех таблиц, которые перечислены в условии. Сначала определите, какие поля нужны для решения и откуда их можно получить. 🎁 Чтобы вам не пришлось искать данные для практики, я сгенерировала синтетический датасет (на Python) и подготовила Jupyter Notebook. Welcome на тренировку !

Как и обещала разбор задачи с собеседования, но... которая разваливается в реальном проде 💥 Иногда на интервью дают вот такую задачу: Посчитай воронку visit -> cart -> purchase. Важно: события должны идти строго по порядку. Кандидаты обычно пишут что-то вроде MIN(CASE WHEN event = 'cart' THEN time END) и сравнивают время первого визита с временем первой корзины. На бумаге звучит логично. Но что произойдет, если пользователь сначала добавил товар в корзину, а уже потом затрекался в приложении (просто события на бэкенде пришли в другом порядке)? Такой пользователь просто выпадет из вашей воронки, хотя фактически прошел её. И COUNT(DISTINCT user_id) тут не спасет, потому что мы отсекаем его на этапе фильтрации по времени. Сейчас все объясню. Создаем искусственный лог событий: Смотрите на User 2. У него сначала идет cart (10:00), потом visit (11:00), и только потом purchase (12:00). import duckdb import pandas as pd data = { 'user_id': [1, 1, 1, 2, 2, 2, 3, 3], 'event_name': ['visit', 'cart', 'purchase', 'cart', 'visit', 'purchase', 'visit', 'cart'], 'event_time': ['2023-10-01 09:00:00', '2023-10-01 10:00:00', '2023-10-01 11:00:00', '2023-10-01 10:00:00', '2023-10-01 11:00:00', '2023-10-01 12:00:00', '2023-10-01 09:00:00', '2023-10-01 10:00:00'] } events = pd.DataFrame(data) events['event_time'] = pd.to_datetime(events['event_time']) events['event_date'] = events['event_time'].dt.date Вариант «Как на собеседовании» (MIN + сравнение времени): Ищем первое событие каждого типа и сравниваем время. query = """ with user_events as ( select user_id, min(case when event_name = 'visit' then event_time end) as visit_time, min(case when event_name = 'cart' then event_time end) as cart_time, min(case when event_name = 'purchase' then event_time end) as purchase_time from events group by user_id ) select count(distinct user_id) as total_visits, count(distinct case when cart_time > visit_time then user_id end) as visit_to_cart_users, count(distinct case when purchase_time > cart_time and cart_time > visit_time then user_id end) as cart_to_purchase_users from user_events where visit_time is not null; """ result1 = duckdb.query(query).to_df() Результат: visit_to_cart_users = 2, cart_to_purchase_users = 2. Мы потеряли User 2! ❌ Вариант - Как в реальности делают в компаниях (Строгий LEFT JOIN по юзеру и периоду): Мы не смотрим на время. Берем уникальные пары (user_id, дата из event_date) для каждого шага и склеиваем их. query = """ with step1 as ( select distinct user_id, cast(event_time as date) as dt from events where event_name = 'visit' ), step2 as ( select distinct user_id, cast(event_time as date) as dt from events where event_name = 'cart' ), step3 as ( select distinct user_id, cast(event_time as date) as dt from events where event_name = 'purchase' ) select count(s1.user_id) as total_visits, count(s2.user_id) as visit_to_cart_users, count(s3.user_id) as cart_to_purchase_users from step1 s1 left join step2 s2 on s2.user_id = s1.user_id and s2.dt = s1.dt left join step3 s3 on s3.user_id = s2.user_id and s3.dt = s2.dt """ result2 = duckdb.query(query).to_df() Результат: visit_to_cart_users = 3, cart_to_purchase_users = 2. Все в порядке, никого не потеряли! ✅ 💡 Почему подход с JOIN - это база: 🟢Он не ломается на нестандартном поведении пользователей. 🟢 Он масштабируется. Если у вас не 3 шага, а 10 (включая просмотр например карточки товара, подтверждения какого-то, открытие корзины и т.д.), писать кучу MIN(CASE WHEN) станет невыносимо. Цепочка LEFT JOIN решает это элегантно. 🟢 Легко управлять окном: джойнить можно по дню, по неделе или по сессии - в зависимости от бизнес-задачи. В реальных дашбордах (например, в Metabase) я строю строгие воронки именно так: сначала собираю CTE для каждого шага, а потом склеиваю их.