Условие

Из таблицы logins(user_id, login_date) посчитайте число «реактивированных» пользователей за каждый месяц. Пользователь считается реактивированным в месяце M, если он залогинился в этом месяце и при этом не логинился ни в M-1, ни ранее в течение какого-то периода — а именно: предыдущий вход был более чем 30 дней назад.

Решение

Подход

Для каждой строки лога считаем разрыв до предыдущего входа этого пользователя через LAG(). Если разрыв > 30 дней — это «возврат». Группируем такие возвраты по месяцу и считаем уникальных пользователей. Первый вход пользователя в системе не считаем реактивацией.

Реализация

WITH gaps AS (
    SELECT
        user_id,
        login_date,
        LAG(login_date) OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ) AS prev_login
    FROM logins
),
reactivations AS (
    SELECT
        user_id,
        login_date,
        DATE_TRUNC('month', login_date)::date AS month
    FROM gaps
    WHERE prev_login IS NOT NULL
      AND login_date - prev_login > INTERVAL '30 days'
)
SELECT
    month,
    COUNT(DISTINCT user_id) AS reactivated_users
FROM reactivations
GROUP BY month
ORDER BY month;

Подводные камни

Первый логин ≠ реактивация. Условие prev_login IS NOT NULL нужно явно — иначе все новички будут засчитаны.
Уникальность пользователя в месяце. Один user может вернуться в месяце дважды (через 31 день и потом сразу). Без COUNT(DISTINCT) получите завышение.
Определение «отток» — бизнес-зависимое. 30 дней? 60? Согласуйте с продактом, и параметризуйте через переменную или CTE.
Несколько логинов в один день — стоит дедуплицировать на уровне дня, иначе LAG сравнит логин в 10:00 с логином в 8:00 того же дня и зашумит результат.

Эталонный ответ

LAG() по user_id, считаете разрыв между логинами; если разрыв > 30 дней — это возврат. Группируете по месяцу и считаете COUNT(DISTINCT user_id). Первый вход в систему не считается реактивацией.

Zachary Thomas: реактивированные пользователи по месяцам