Как посчитать Days Since Last Login в SQL
DISTINCT в SELECT-запросе?Содержание:
Зачем считать дни с последнего входа
Days Since Last Login — число дней, прошедших с последнего входа пользователя. Это индивидуальный сигнал предстоящего оттока: в отличие от агрегированного churn, который считается по всей базе, эта метрика существует для каждого пользователя отдельно.
Ценность в том, что по ней можно строить скоринг риска и точно таргетировать реактивационные кампании. Пользователь не заходил 15 дней — он ещё не churned, но уже остывает, и это лучший момент вернуть его письмом или пушем. Не заходил 120 дней — реактивация почти безнадёжна, тратить на него бюджет бессмысленно. Метрика превращает размытое «часть базы уходит» в конкретный список: кому и когда слать.
Дальше — готовые запросы под PostgreSQL, которые можно вставить в Metabase или dbt.
Базовый расчёт
Считаем по каждому пользователю дату последнего входа и разницу с сегодняшним днём:
WITH last_login AS (
SELECT
user_id,
MAX(login_time) AS last_login_at,
CURRENT_DATE - MAX(login_time)::DATE AS days_since
FROM logins
GROUP BY user_id
)
SELECT
user_id,
last_login_at,
days_since
FROM last_login
WHERE days_since > 30 -- фильтр «остывших»: не заходили больше месяца
ORDER BY days_since DESC;MAX(login_time) берёт самый свежий вход, приведение ::DATE отбрасывает время, а вычитание из CURRENT_DATE даёт целое число дней. Это фундамент — всё остальное строится поверх.
Распределение по бакетам
Сырое число дней по каждому пользователю неинформативно — важна картина по базе целиком. Разложим пользователей по интервалам «давности» и посчитаем долю каждого:
WITH last_login AS (
SELECT user_id, CURRENT_DATE - MAX(login_time)::DATE AS days_since
FROM logins
GROUP BY user_id
)
SELECT
CASE
WHEN days_since = 0 THEN 'сегодня'
WHEN days_since <= 1 THEN '1 день'
WHEN days_since <= 7 THEN '2-7 дней'
WHEN days_since <= 30 THEN '8-30 дней'
WHEN days_since <= 90 THEN '31-90 дней'
WHEN days_since <= 180 THEN '91-180 дней'
ELSE '180+ дней'
END AS bucket,
COUNT(*) AS users,
COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct
FROM last_login
GROUP BY bucket
ORDER BY MIN(days_since);Оконная функция SUM(COUNT(*)) OVER () даёт общий знаменатель, чтобы посчитать процент от всей базы. Если хвост «180+ дней» разросся — база стареет, приток новых не перекрывает отток старых.
Сегментация по риску
Бакеты можно сразу переименовать в бизнес-сегменты риска оттока — так результат читается менеджментом без перевода:
WITH user_status AS (
SELECT
user_id,
CURRENT_DATE - MAX(login_time)::DATE AS days_since_login,
CASE
WHEN CURRENT_DATE - MAX(login_time)::DATE <= 7 THEN 'активный'
WHEN CURRENT_DATE - MAX(login_time)::DATE <= 14 THEN 'под риском'
WHEN CURRENT_DATE - MAX(login_time)::DATE <= 30 THEN 'спящий'
WHEN CURRENT_DATE - MAX(login_time)::DATE <= 90 THEN 'почти ушёл'
ELSE 'ушёл'
END AS risk_segment
FROM logins
GROUP BY user_id
)
SELECT
risk_segment,
COUNT(*) AS users,
COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct
FROM user_status
GROUP BY risk_segment
ORDER BY MIN(days_since_login);Границы порогов — не универсальны: для ежедневного продукта «под риском» начинается уже с 3-5 дней, для продукта с недельным циклом использования — с двух-трёх недель. Пороги подбирают под нормальную частоту возврата в конкретном продукте.
Разрез по когортам
Чтобы увидеть, стал ли продукт лучше удерживать, смотрят метрику в разрезе когорт регистрации:
WITH user_login AS (
SELECT
u.user_id,
DATE_TRUNC('month', u.created_at) AS cohort,
CURRENT_DATE - MAX(l.login_time)::DATE AS days_since_login
FROM users u
LEFT JOIN logins l USING (user_id) -- LEFT JOIN: не потерять тех, кто ни разу не заходил
GROUP BY u.user_id, DATE_TRUNC('month', u.created_at)
)
SELECT
cohort,
COUNT(*) AS users,
AVG(days_since_login) AS avg_days_since,
COUNT(*) FILTER (WHERE days_since_login <= 7)::NUMERIC * 100 / COUNT(*) AS active_pct,
COUNT(*) FILTER (WHERE days_since_login > 90)::NUMERIC * 100 / COUNT(*) AS churned_pct
FROM user_login
GROUP BY cohort
ORDER BY cohort;LEFT JOIN здесь принципиален: он оставляет в выборке пользователей вообще без входов. Если свежие когорты держат более высокий active_pct, чем старые на том же возрасте, — продукт улучшил онбординг и удержание.
Триггер для реактивации
Финальный практический запрос — вытащить пользователей ровно в том окне, когда реактивация ещё работает, вместе с их email для рассылки:
SELECT
u.user_id,
u.email,
(CURRENT_DATE - MAX(l.login_time)::DATE) AS days_inactive
FROM users u
JOIN logins l USING (user_id)
WHERE u.email IS NOT NULL
GROUP BY u.user_id, u.email
HAVING CURRENT_DATE - MAX(l.login_time)::DATE BETWEEN 14 AND 21 -- окно реактивации
ORDER BY MAX(l.login_time);Окно 14-21 день — распространённый sweet spot: раньше письмо раздражает ещё активного пользователя, позже он уже остыл настолько, что не откликнется.
Как это спрашивают на собесе
«Как найти пользователей на грани оттока?» Сильный ответ: посчитать дни с последнего входа через CURRENT_DATE - MAX(login_time)::DATE, отфильтровать по порогу под частоту возврата в продукте. Не путать с самим churn — это опережающий сигнал, а не свершившийся факт.
«Чем логин отличается от активности?» Пользователь может быть активен без явного входа — через открытые пуши, API, фоновую сессию. Считать только по логинам — значит переоценить отток. Правильнее мерить по любому событию активности.
«Как учесть тех, кто ни разу не заходил?» У них последнего входа нет, разница будет NULL. Их выделяют в отдельный сегмент — это провал активации, а не отток, и лечится он по-другому.
Частые ошибки
Логин вместо активности. Пользователь может пользоваться продуктом без явного входа — через API, пуши, уже открытую сессию. Считать только по логинам — занижать активность. Правильнее мерить по любому событию активности, а не по факту входа.
Пользователи без единого входа. У них days_since будет NULL, и они молча выпадут из JOIN или дадут пустой сегмент. Их либо явно фильтруют, либо выделяют в отдельную корзину «не активировались» — это разные бизнес-задачи, чем реактивация.
Часовые пояса. Если login_time хранится в UTC, а CURRENT_DATE берётся в локальной зоне сервера, расчёт может ошибиться на день. Хранить время в UTC, конвертировать в зону пользователя только на этапе отчётности.
Мультидевайс. Последний вход был на десктопе, но пользователь активен с мобильного под другой записью логина — метрика покажет ложную «давность». Записи разных устройств одного пользователя нужно объединять по user_id.
Авто-логин и SSO. Считать ли обновление сессии по SSO за «вход»? От этого зависит цифра. Определение фиксируют заранее: например, засчитывать только осознанные входы, а не фоновые рефреши токена.
Связанные темы
- Как посчитать inactive users в SQL
- Как посчитать churn в SQL
- Как посчитать reactivation в SQL
- Как посчитать win-back rate в SQL
FAQ
Дни с последнего входа или с последней активности — что считать?
Зависит от продукта. Логин — узкий сигнал: он не ловит пользователей, которые открывают пуши или пользуются продуктом без явного входа. Активность шире и точнее отражает вовлечённость. Если у продукта есть заметное «использование без логина», считать надо по активности, иначе отток будет завышен.
Какой порог брать для реактивации?
Обычно 14-30 дней — sweet spot. Раньше письмо раздражает ещё активного пользователя, позже он уже остыл и не откликнется. Точное окно подбирают под нормальную частоту возврата: у ежедневного продукта оно короче, у продукта с редким использованием — длиннее.
Что делать с пользователями без единого входа?
Это, скорее всего, провал активации, а не отток. Их выделяют в отдельную когорту и работают с ними через онбординг, а не через реактивацию. Смешивать их с «ушедшими» нельзя — это разные проблемы и разные решения.
Влияет ли часовой пояс на расчёт?
Да, и это частый источник ошибки на день. Храните время входа в UTC, а конвертацию в локальную зону пользователя делайте только на этапе отчётности. Иначе граница суток «поедет», и метрика будет систематически смещена.
Как склеить входы с разных устройств?
По стабильному user_id, если пользователь авторизован на всех устройствах. Если авторизации нет и записи анонимные, приходится сопоставлять личности через fingerprint или identity resolution — это отдельная и менее надёжная задача, о её ограничениях стоит сказать честно.