Мир аналитика данных

Задачка с собеседования: как найти объявления с растущими ценами?
Часто в аналитике возникает задача: есть история изменения цен (снимки/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 для каждого шага, а потом склеиваю их.