Как посчитать новых пользователей и их активность в SQL запросе?
Что проверяют
Умеете ли вы превратить расплывчатую метрику в точное определение до того, как писать запрос. Интервьюер ждёт вопросов: кто считается новым, что считается активностью и за какое окно. Следом обычно просят разбить результат по когортам или посчитать долю активных.
Как отвечать
Зафиксируйте определения вслух:
- новый пользователь: зарегистрировался в отчётном периоде, дата берётся из
users.registered_at, а не из таблицы событий; - активность: число событий нужного типа или число активных дней за первые N дней после регистрации. Одинаковое окно для всех делает пользователей сравнимыми, иначе зарегистрированный 1-го успевает накопить больше действий, чем зарегистрированный 30-го;
- метрики: количество новых, количество и доля активных среди них, среднее число событий на пользователя.
Каркас запроса: CTE new_users выбирает пользователей с registered_at >= '2026-08-01' AND registered_at < '2026-09-01'. Затем LEFT JOIN events e ON e.user_id = u.user_id AND e.event_time < u.registered_at + INTERVAL '7 days'. В итоговом SELECT считайте COUNT(DISTINCT u.user_id) как всех новых, COUNT(DISTINCT e.user_id) как активных и COUNT(e.event_id) как число событий. Долю активных получите делением с приведением к numeric, иначе целочисленное деление в PostgreSQL даст 0. Для когорт добавьте GROUP BY date_trunc('week', u.registered_at).
Скажите и про диалект: date_trunc и INTERVAL из PostgreSQL, в T-SQL это DATEADD и DATETRUNC, в ClickHouse toStartOfWeek.
Где спотыкаются
Берут новых пользователей из таблицы событий через INNER JOIN. Пользователи без единого действия выпадают, и доля активных внезапно становится 100%.
Ставят условие на события в WHERE после LEFT JOIN. Строки с NULL отсекаются, и соединение фактически превращается во внутреннее. Условию по правой таблице место в ON.
Используют BETWEEN для дат со временем: BETWEEN '2026-08-01' AND '2026-08-31' теряет весь последний день после полуночи. Полуоткрытый интервал через >= и < надёжнее, и заодно проверьте, в каком часовом поясе хранится время.