Условие

Таблица logins(user_id, login_date) — даты, когда юзер заходил. Для каждого пользователя найти самый длинный streak подряд идущих дней.

Решение

Идея gaps-and-islands

Если несколько дат идут подряд (например, 10, 11, 12 мая), то их разность с порядковым номером — константа: 10 - 1 = 9, 11 - 2 = 9, 12 - 3 = 9. Группируем по этой константе → получаем «острова».

SQL (PostgreSQL)

WITH dedupe AS (
    SELECT DISTINCT user_id, login_date FROM logins
),
ranked AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
        login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
    FROM dedupe
),
streaks AS (
    SELECT
        user_id,
        grp,
        MIN(login_date) AS streak_start,
        MAX(login_date) AS streak_end,
        COUNT(*) AS streak_length
    FROM ranked
    GROUP BY user_id, grp
)
SELECT user_id, MAX(streak_length) AS longest_streak,
       (SELECT streak_start FROM streaks s2
        WHERE s2.user_id = s.user_id
        ORDER BY streak_length DESC LIMIT 1) AS streak_start
FROM streaks s
GROUP BY user_id;

Альтернатива через LAG

WITH flagged AS (
    SELECT
        user_id,
        login_date,
        CASE
            WHEN LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)
                 = login_date - INTERVAL '1 day'
            THEN 0 ELSE 1
        END AS new_streak
    FROM (SELECT DISTINCT user_id, login_date FROM logins) d
),
groups AS (
    SELECT
        user_id, login_date,
        SUM(new_streak) OVER (PARTITION BY user_id ORDER BY login_date) AS grp
    FROM flagged
)
SELECT user_id, MAX(streak_length) AS longest
FROM (
    SELECT user_id, grp, COUNT(*) AS streak_length
    FROM groups GROUP BY user_id, grp
) sub
GROUP BY user_id;

Текущий streak (до today)

SELECT user_id, streak_length AS current_streak
FROM streaks
WHERE streak_end = CURRENT_DATE   -- или MAX(login_date) пользователя
ORDER BY current_streak DESC;

Производительность

Window function over partition user_id хорошо параллелится.
Индекс (user_id, login_date) обязателен.
На миллиарде строк — рассматривайте ClickHouse / Spark.

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

Дубли дат (несколько входов в день): без DISTINCT row_number смещается, формула ломается.
Tz: если login_date — это timestamp, переведите в DATE сначала.
Пропуск выходных можно считать «активным» (тогда стрик не ломается) — другая логика, требует переоформления.
ClickHouse: вместо ROW_NUMBER() — rowNumberInAllBlocks или явный JOIN.
NULL в login_date ломает LAG — фильтруйте.

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

Сначала DISTINCT (user_id, date). Затем grp = date - row_number() — внутри одного острова grp одинаков. GROUP BY user_id, grp → длина стрика, MAX по юзеру = longest.

Сценарий: gaps and islands — самый длинный streak входов пользователя