Как построить воронку с фильтрацией по первому визиту и подсчётом пользователей со вторым визитом в интервале?

Что проверяют

Умеете ли вы превратить продуктовый вопрос в корректный SQL: определить когорту по первому визиту, найти следующий визит и не потерять знаменатель. Следом обычно просят посчитать долю вернувшихся по дням когорты или поменять окно на 7 и 30 дней.

Как отвечать

Сначала договоритесь об определениях, это половина ответа: что считается визитом (сессия или хит), считаются ли повторы в тот же день, в каком часовом поясе режем даты.

Дальше каркас на оконных функциях:

• нумерация визитов: row_number() over (partition by user_id order by visit_ts) в CTE, первый визит это rn = 1, следующий rn = 2;
• фильтр когорты: оставляем пользователей, у которых первый визит попал в изучаемый период, например в сентябрь;
• второй визит в окне: присоединяем rn = 2 через left join с условием second.visit_ts <= first.visit_ts + interval '7 day' прямо в on;
• итог: count(*) по когорте, count(second.user_id) по вернувшимся и их отношение. Долю считайте через 100.0 * ..., иначе целочисленное деление даст ноль.

Окно задаётся относительно первого визита каждого пользователя, а не фиксированными датами календаря. Если окно для свежих пользователей ещё не закрылось, их исключают из когорты, иначе конверсия за последние дни будет искусственно низкой.

Для воронки из нескольких шагов логика та же: на каждом шаге ищем событие после предыдущего в пределах окна. В ClickHouse для этого есть готовая функция windowFunnel.

Где спотыкаются

Ставят фиксированный BETWEEN '2023-01-01' AND '2023-01-31' на второй визит. Это уже не «вернулся в течение недели», а «заходил в январе», и у пользователей с первым визитом 30-го почти нет шанса попасть в числитель.

Теряют невернувшихся: берут inner join или пишут условие окна в where после left join, что превращает его во внутреннее соединение. Доля считается от неправильного знаменателя.

Забывают про дубли: два события с одной меткой времени или несколько хитов одной сессии дают фальшивый «второй визит». Дедупликация или сессионизация нужна до нумерации.