SQL: как найти серии последовательных дней без ошибок на пропусках

Продуктовая команда просит показать, сколько дней подряд пользователи возвращались в сервис. В журнале событий есть 1, 2, 4 и 5 марта. Обычная группировка по пользователю посчитает четыре активных дня, но не заметит разрыв 3 марта. Нужно получить две серии: 1–2 и 4–5 марта.

У событий есть ещё одна особенность: за день пользователь может открыть приложение много раз. Если сначала пронумеровать сырые события, повтор 2 марта сдвинет номера следующих строк и исказит серию. Поэтому первым шагом оставляем по одной строке на пару «пользователь — календарная дата».

Как дата и номер строки находят один остров

Отсортируем уникальные дни пользователя и дадим им номера 1, 2, 3… Для соседних календарных дней значение «числовая дата минус номер строки» одинаково: обе части растут на единицу. После пропуска календарная дата увеличится сильнее номера, и получится новый ключ группы. Это и есть приём gaps and islands: пропуски разделяют непрерывные участки.

В SQLite календарную дату можно перевести в число дней через julianday(). Ниже полный запрос вместе с маленьким входным набором. Даты записаны как YYYY-MM-DD; для событий с временем сначала приведите отметку к нужной календарной зоне, затем выделяйте дату.

CREATE TABLE visits (user_id INTEGER, visited_on TEXT);
INSERT INTO visits VALUES
  (7, '2025-03-01'), (7, '2025-03-02'),
  (7, '2025-03-02'), (7, '2025-03-04'),
  (7, '2025-03-05'), (8, '2025-03-02');

WITH days AS (
  SELECT DISTINCT user_id, visited_on FROM visits
), numbered AS (
  SELECT user_id, visited_on,
         ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY visited_on
         ) AS rn
  FROM days
), grouped AS (
  SELECT user_id, visited_on,
         CAST(julianday(visited_on) AS INTEGER) - rn AS grp
  FROM numbered
)
SELECT user_id, MIN(visited_on) AS first_day,
       MAX(visited_on) AS last_day, COUNT(*) AS days_in_streak
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, first_day;

Результат для пользователя 7: 2025-03-01—2025-03-02, 2 дня и 2025-03-04—2025-03-05, 2 дня. Для пользователя 8 получится отдельная серия 2025-03-02—2025-03-02, 1 день. Повтор 2 марта не увеличивает длину первой серии благодаря DISTINCT.

Важно, что номер начинается заново для каждого пользователя: за это отвечает PARTITION BY user_id. А GROUP BY user_id, grp не склеивает серии разных людей, даже когда вычисленный ключ случайно совпал. MIN и MAX задают границы, COUNT(*) после удаления дублей становится числом активных дней.

Когда правило соседства другое

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

На реальном журнале прежде всего проверьте зерно исходной таблицы: событие, день пользователя или уже агрегированная строка. Затем сравните несколько ручных последовательностей, где есть дубль, пропущенный день и граница месяца. По ним сразу видно, отвечает ли запрос выбранному правилу.

Если нужен только самый длинный streak каждого пользователя, не меняйте внутреннюю группировку. Сначала получите все серии, как в примере, затем пронумеруйте их через ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY days_in_streak DESC, last_day DESC) и оставьте номер 1. Так при равной длине победит более поздняя серия. Без второго критерия выбор при равенстве зависит от порядка, который запрос не определил.

Для текущей серии отдельное правило: её последний день должен совпадать с датой, относительно которой вы строите отчёт. Если разрешаете вчерашнюю серию для пользователя, который ещё не успел прийти сегодня, это тоже надо явно записать в условии. Иначе «максимальная историческая серия» легко превращается в ошибочно названную «текущую». Тот же вычисленный ключ группы остаётся полезным; меняется фильтр по последнему дню.

В «Продвинутом SQL» эту идею можно развить до пользовательских сессий, пересекающихся интервалов, retention и календарных рядов с пропусками. В курсе есть оконные функции и SQL-задачи с проверкой на нескольких наборах данных; итоговая работа связывает приёмы в витрину интернет-магазина. Откройте программу, чтобы пройти от короткого запроса к полноценному аналитическому расчёту.

Если дальше нужен скользящий показатель поверх дневного ряда, пригодится статья о скользящем среднем при пропущенных днях.