SQL: Найти пользователей с непрерывной активностью N дней

SQL: Найти пользователей с непрерывной активностью N дней

Объяснение подхода "Gaps and Islands"

Основная идея — сгруппировать непрерывные даты в "острова" (islands), отделённые "разрывами" (gap). Для этого используем хитрость: вычитаем из даты номер строки ROW_NUMBER(), получая "группу дней".

Логика: если дни идут подряд, разница между датой и ROW_NUMBER() будет одинаковой для всей последовательности.

Решение

WITH dated_logins AS (
    -- Избавляемся от дубликатов и добавляем номер строки
    SELECT DISTINCT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as rn
    FROM user_logins
),
streaks AS (
    -- Определяем группы непрерывных дней (island groups)
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) as streak_group,
        rn
    FROM dated_logins
),
streak_stats AS (
    -- Подсчитываем длину каждого "острова"
    SELECT
        user_id,
        streak_group,
        MIN(login_date) as streak_start_date,
        MAX(login_date) as streak_end_date,
        COUNT(*) as streak_length
    FROM streaks
    GROUP BY user_id, streak_group
)
SELECT
    user_id,
    streak_start_date,
    streak_end_date,
    streak_length
FROM streak_stats
WHERE streak_length >= 7
ORDER BY user_id, streak_start_date;

Как это работает пошагово

Шаг 1: dated_logins — убираем дубликаты и нумеруем логины пользователя по порядку Шаг 2: streaks — вычисляем "группу полос" вычитанием ROW_NUMBER() из даты Шаг 3: streak_stats — группируем по полосе и подсчитываем дни Шаг 4: финальный SELECT фильтрует полосы длиной >= 7 дней

Пример работы

Если пользователь логинился: 2024-01-01, 2024-01-02, 2024-01-03, 2024-01-05, 2024-01-06:

• Дни 01-03 образуют одну полосу (разница дат = ROW_NUMBER)
• День 05-06 образуют вторую полосу
• Только полосы с >= 7 днями выводятся

Альтернатива для PostgreSQL

WITH streaks AS (
    SELECT
        user_id,
        login_date,
        COUNT(*) FILTER (WHERE gap IS NULL) 
            OVER (PARTITION BY user_id ORDER BY login_date) as streak_group
    FROM (
        SELECT
            user_id,
            login_date,
            CASE WHEN login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) = 1 
                 THEN NULL ELSE 1 END as gap
        FROM (SELECT DISTINCT user_id, login_date FROM user_logins) t
    ) t
    GROUP BY user_id, login_date, gap
)
SELECT
    user_id,
    MIN(login_date) as streak_start_date,
    MAX(login_date) as streak_end_date,
    COUNT(*) as streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 7
ORDER BY user_id;