Как посчитать Days Since Last Login в SQL

Зачем Days Since Last Login

«Days Since Last Login» = индивидуальный пре-churn сигнал. Per-user metric. Можно scoring → re-engagement campaigns target accurately.

Базовый расчёт

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;

Distribution

Histogram of days inactive:

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 'today'
        WHEN days_since <= 1 THEN '1 day'
        WHEN days_since <= 7 THEN '2-7 days'
        WHEN days_since <= 30 THEN '8-30 days'
        WHEN days_since <= 90 THEN '31-90 days'
        WHEN days_since <= 180 THEN '91-180 days'
        ELSE '180+ days'
    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);

Сегментация по риску

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 'active'
            WHEN CURRENT_DATE - MAX(login_time)::DATE <= 14 THEN 'at-risk'
            WHEN CURRENT_DATE - MAX(login_time)::DATE <= 30 THEN 'dormant'
            WHEN CURRENT_DATE - MAX(login_time)::DATE <= 90 THEN 'likely-churned'
            ELSE 'churned'
        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);

По cohort

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)
    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;

Trigger for re-engagement

-- Users в "at-risk" window для targeted 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  -- sweet spot
ORDER BY MAX(l.login_time);

Частые ошибки

Ошибка 1. Login vs Activity. User can be active без login (e.g. API access, push notifications). Track activity not just login.

Ошибка 2. Never-logged-in users. NULL days_since. Filter or special bucket.

Ошибка 3. Time-zone. last_login_at в UTC vs CURRENT_DATE в local — может быть 1 day off.

Ошибка 4. Multi-device. Last login на desktop, but active on mobile (different login records). Merge.

Ошибка 5. Auto-login / SSO. SSO refresh = login event? Depending on definition.

Связанные темы

• Как посчитать inactive users в SQL
• Как посчитать churn в SQL
• Как посчитать reactivation в SQL
• Как посчитать win-back rate в SQL

FAQ

Days since last login vs last activity?

Login — narrow. Activity — broader (includes opens, app use без login).

Threshold для re-engagement?

14-30 days — sweet spot. Too early annoying, too late churned.

Never-logged users?

Likely failed activation. Separate cohort.

Time-zone matter?

Yes — store в UTC, convert при reporting в user's tz.

Multi-device merge?

By user_id (если stable login). Без login — fingerprint-based ID resolution.