Как найти цепочки подряд в SQL

Зачем это знать

Streak — ключевая метрика в Duolingo, Карьернике, fitness-приложениях. «Сколько дней подряд пользователь заходил» — прямая мера retention. В SQL посчитать streak сложно без правильного pattern.

На собесах это — known «gaps and islands» pattern. Middle+ аналитики должны уметь.

Задача

Дана таблица logins с user_id и day. Найти максимальный streak (дни подряд) для каждого пользователя.

| user_id | day        |
| 1       | 2026-04-01 |
| 1       | 2026-04-02 |
| 1       | 2026-04-03 |
| 1       | 2026-04-05 |
| 1       | 2026-04-06 |

User 1: streak 3 дня (01-03), потом 2 (05-06).

Трюк: gaps and islands

Ключевая идея: если из day вычесть ROW_NUMBER(), то подряд идущие дни дадут одинаковую разницу.

day         rn    day - rn
2026-04-01  1     2026-03-31
2026-04-02  2     2026-03-31  ← same group
2026-04-03  3     2026-03-31  ← same group
2026-04-05  4     2026-04-01  ← new group
2026-04-06  5     2026-04-01  ← same group

Группируем по day - rn → получаем «острова».

Запрос

WITH numbered AS (
    SELECT user_id, day,
           day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
    FROM logins
),
islands AS (
    SELECT user_id, grp, MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS streak_len
    FROM numbered
    GROUP BY user_id, grp
)
SELECT user_id, MAX(streak_len) AS max_streak
FROM islands
GROUP BY user_id;

Результат: user_id → max streak length.

Текущий streak

Актуальный streak (до сегодня):

SELECT user_id, streak_len
FROM islands
WHERE end_day = CURRENT_DATE;

Или end_day >= CURRENT_DATE - 1 если вчера считается.

Не-date данные

Не только даты. Например, consecutive wins в игре:

WITH t AS (
    SELECT game_id, is_win,
           game_id - ROW_NUMBER() OVER (PARTITION BY is_win ORDER BY game_id) AS grp
    FROM games
    WHERE user_id = 123
)
SELECT is_win, MIN(game_id), MAX(game_id), COUNT(*) AS streak
FROM t
WHERE is_win = 1
GROUP BY is_win, grp
ORDER BY streak DESC LIMIT 1;

Максимальная победная серия.

Варианты задач

Streak с допуском (skip-days)

«Разрешено пропустить 1 день» — формула сложнее. Через LAG + флаг + cumulative sum.

Streak по event type

«7 дней подряд был SQL-ответ» — фильтровать по topic, потом gaps and islands.

Через LAG (альтернатива)

WITH t AS (
    SELECT user_id, day,
           CASE WHEN day = LAG(day) OVER (PARTITION BY user_id ORDER BY day) + 1
                THEN 0 ELSE 1 END AS is_new_group
    FROM logins
),
grouped AS (
    SELECT user_id, day,
           SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY day) AS grp
    FROM t
)
SELECT user_id, grp, COUNT(*) AS streak FROM grouped GROUP BY 1, 2;

Явно видна логика, но сложнее.

На собесе

«Сколько дней подряд заходил user X максимум?».

Алгоритм:

1. Сформулировать gaps and islands
2. ROW_NUMBER per user
3. day - rn = group key
4. GROUP BY + COUNT + MAX

Озвучить это по шагам — +1 балл.

Использование

В продукте

• Streak badges (Duolingo)
• Retention metric (X дней подряд)
• Fraud detection (N действий подряд за секунду)

В аналитике

• Длина сессий (события без gap > 30 мин)
• Consecutive failures/successes

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

• 50 вопросов по SQL
• Задачи SQL на собесе
• Оконные функции

FAQ

Это gaps and islands?

Да, классический pattern. Streaks = islands.

Работает для часов/минут?

Да. Вместо +1 дня — +1 час или +1 минута (через интервалы).

С DISTINCT нужно?

Если пользователь может залогиниться 2 раза в день — сделайте DISTINCT day в CTE.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.