Gaps-and-islands в SQL: серии подряд идущих дней

Gaps-and-islands — это шаблон запроса, который отвечает на вопрос «сколько раз подряд?». Стрик активности пользователя, непрерывный период подписки без разрывов, серия дней с продажами — всё это одна и та же задача. Приём в основе один: пронумеровать строки внутри группы через ROW_NUMBER(), вычесть номер из даты (или из порядкового значения), получить константу-ключ для каждой непрерывной серии и сгруппировать по этому ключу. Если запомнить именно эту связку — «разность фиксирована внутри серии» — вы закроете 80% задач на стрики на любом собесе. Ниже разберём почему это работает и три рабочих примера на PostgreSQL с данными.

Почему это вообще задача

Реляционная модель не хранит «порядок». Строки в таблице — множество, а не список. Когда аналитику нужно найти «5 дней подряд с покупками», SQL сам по себе не знает, что 3 марта идёт после 2 марта «вплотную». Приходится это вычислять.

Наивный подход — тащить всё в Python, отсортировать, пройти циклом и считать разрывы. Работает на тысяче строк, разваливается на десятках миллионов: вы перекачиваете таблицу целиком по сети только чтобы посчитать длину серии. Gaps-and-islands делает то же самое одним GROUP BY прямо в базе, где данные уже лежат.

Термины: islands (острова) — это непрерывные серии, то что мы ищем. gaps (разрывы) — дырки между ними. На собесе чаще спрашивают именно острова: «самый длинный стрик», «текущий стрик», «все периоды непрерывной подписки».

Ключевая идея на пальцах

Возьмём даты активности одного пользователя: 1, 2, 3, 6, 7, 10 марта. Глазами видно три острова: [1,2,3], [6,7], [10]. Как это увидит SQL?

Пронумеруем даты по порядку — ROW_NUMBER() даёт 1, 2, 3, 4, 5, 6. Теперь вычтем номер строки из числа месяца:

Смотрите на последний столбец. Внутри одного острова разность постоянна. Пока дни идут подряд (шаг +1) и номер строки тоже растёт на +1, их разность не меняется. Как только появляется разрыв — день прыгает больше чем на 1, а номер строки всё равно +1 — разность скачет. Значит эта разность и есть идентификатор острова. Группируем по ней — получаем серии.

Это вся магия. Дальше — только вариации под конкретный тип данных (даты, целые числа, статусы).

Формально: если $d_i$ — значение $i$-й строки в отсортированном ряду, а $r_i$ — её ROW_NUMBER(), то для строк одного острова с шагом 1 выполняется $d_i - r_i = \text{const}$, потому что и $d$, и $r$ растут синхронно на единицу.

Пример 1. Стрик активности пользователя

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

Данные:

CREATE TABLE activity (
    user_id  int,
    log_date date
);

INSERT INTO activity VALUES
(1, '2026-03-01'), (1, '2026-03-02'), (1, '2026-03-03'),
(1, '2026-03-06'), (1, '2026-03-07'),
(1, '2026-03-10'),
(2, '2026-03-01'), (2, '2026-03-02');

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

WITH daily AS (
    SELECT DISTINCT user_id, log_date
    FROM activity
),
grouped AS (
    SELECT
        user_id,
        log_date,
        log_date - (ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY log_date
        ))::int AS grp
    FROM daily
)
SELECT
    user_id,
    MIN(log_date)            AS streak_start,
    MAX(log_date)            AS streak_end,
    COUNT(*)                 AS streak_length
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

В PostgreSQL date - integer возвращает дату, сдвинутую на N дней. Поскольку grp для строк одного острова получается одинаковой датой, GROUP BY user_id, grp собирает каждую непрерывную серию в одну строку. Результат:

Хотите самый длинный стрик по каждому пользователю — оберните ещё одним уровнем:

WITH daily AS (
    SELECT DISTINCT user_id, log_date FROM activity
),
grouped AS (
    SELECT user_id, log_date,
           log_date - (ROW_NUMBER() OVER (
               PARTITION BY user_id ORDER BY log_date))::int AS grp
    FROM daily
),
streaks AS (
    SELECT user_id, COUNT(*) AS len
    FROM grouped
    GROUP BY user_id, grp
)
SELECT user_id, MAX(len) AS max_streak
FROM streaks
GROUP BY user_id;

Разобраться с оконными функциями до автоматизма помогает интерактивный гайд /sql/window — там разбор ROW_NUMBER, RANK, LAG с автопроверкой прямо в браузере.

Пример 2. Непрерывные периоды подписки

Тут поворот: разрывы нужно искать не по дням, а по фактам — подписка активна в марте, апреле, потом дыра в мае, снова июнь-июль. Данные помесячные, и часто уже есть готовый порядковый номер периода или строки идут «по одной на месяц». Когда шаг ряда — целое число (номер месяца, а не дата), вычитаем ROW_NUMBER() напрямую.

CREATE TABLE subscriptions (
    user_id     int,
    period_ym   text,   -- 'YYYY-MM'
    is_active   boolean
);

INSERT INTO subscriptions VALUES
(1, '2026-01', true), (1, '2026-02', true), (1, '2026-03', true),
(1, '2026-04', false),
(1, '2026-05', true), (1, '2026-06', true);

Нужны непрерывные интервалы, где is_active = true. Отфильтровываем активные, переводим YYYY-MM в число месяцев от точки отсчёта, вычитаем номер строки:

WITH active AS (
    SELECT
        user_id,
        period_ym,
        (split_part(period_ym, '-', 1)::int * 12
         + split_part(period_ym, '-', 2)::int) AS month_num
    FROM subscriptions
    WHERE is_active
),
grouped AS (
    SELECT
        user_id,
        period_ym,
        month_num - ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY month_num
        ) AS grp
    FROM active
)
SELECT
    user_id,
    MIN(period_ym) AS period_start,
    MAX(period_ym) AS period_end,
    COUNT(*)       AS months_active
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, period_start;

Апрель с is_active = false выпал на фильтре, разорвал ряд, и подписка честно разбилась на два периода. Именно так считают «сколько раз клиент возвращался после churn» и метрики удержания — про сами метрики есть разборы формул в /metrics/retention_d7.

Пример 3. Серии по смене статуса (без пропусков в ряду)

Третий вариант — когда ряд плотный (нет дыр в датах), но нужно найти серии одинакового статуса. Например, лог заказов день за днём, и нужны непрерывные полосы дней, где выручка была выше плана. Здесь работает разность двух ROW_NUMBER() — приём, который стоит знать отдельно, потому что он спасает, когда простое вычитание не годится.

CREATE TABLE daily_sales (
    sale_date date,
    above_plan boolean
);

INSERT INTO daily_sales VALUES
('2026-03-01', true), ('2026-03-02', true),
('2026-03-03', false),
('2026-03-04', true), ('2026-03-05', true), ('2026-03-06', true),
('2026-03-07', false);

Идея: нумеруем строки дважды — один раз по всему ряду, второй раз внутри каждого статуса. Разность этих номеров постоянна внутри одной непрерывной полосы одинакового статуса.

WITH numbered AS (
    SELECT
        sale_date,
        above_plan,
        ROW_NUMBER() OVER (ORDER BY sale_date) AS rn_all,
        ROW_NUMBER() OVER (
            PARTITION BY above_plan ORDER BY sale_date
        ) AS rn_status
    FROM daily_sales
)
SELECT
    above_plan,
    MIN(sale_date) AS run_start,
    MAX(sale_date) AS run_end,
    COUNT(*)       AS run_length
FROM numbered
GROUP BY above_plan, rn_all - rn_status
ORDER BY run_start;

Разность rn_all - rn_status — тот самый ключ острова. Пока статус не меняется, оба счётчика растут синхронно и разность держится. Меняется статус — rn_status перезапускает счёт внутри своей партиции, разность скачет, начинается новая полоса.

Этот вариант — «две нумерации» — универсальнее первого, потому что не требует чтобы значения были числовыми и шли с шагом 1. Ему достаточно, чтобы ряд был плотным по сортировке.

Альтернатива через LAG — когда её выбирать

Есть второй способ находить границы островов — через LAG(): смотрим на предыдущую строку, и если разрыв больше допустимого — это начало нового острова. Потом накопительной суммой нумеруем острова.

WITH marked AS (
    SELECT
        user_id, log_date,
        CASE
            WHEN log_date - LAG(log_date) OVER (
                     PARTITION BY user_id ORDER BY log_date) = 1
            THEN 0 ELSE 1
        END AS is_new_island
    FROM (SELECT DISTINCT user_id, log_date FROM activity) d
),
islands AS (
    SELECT user_id, log_date,
           SUM(is_new_island) OVER (
               PARTITION BY user_id ORDER BY log_date) AS island_id
    FROM marked
)
SELECT user_id, MIN(log_date) AS start_d, MAX(log_date) AS end_d, COUNT(*) AS len
FROM islands
GROUP BY user_id, island_id;

Способ через ROW_NUMBER() короче и чаще всплывает на собесах как «эталонный». Способ через LAG() — гибче, когда «непрерывность» определяется хитрым правилом (разрыв до 3 дней ещё считается той же серией, разные пороги для будней и выходных). Оба дают одинаковый результат, знать полезно оба — на собесе фраза «а можно ещё через LAG» показывает, что вы поняли суть, а не заучили один трюк.

Типичные грабли

Забыли DISTINCT. Если в логе несколько записей за день, ROW_NUMBER() пронумерует дубликаты, разность поедет, серии сломаются. Всегда схлопывайте до одной строки на единицу шага.

Забыли PARTITION BY. Без партиции по пользователю нумерация сквозная — серии разных людей склеятся. Проверяйте, что партиция стоит везде, где серии считаются в разрезе сущности.

ROW_NUMBER вместо DENSE_RANK при дублях в сортировке. Если сортируете по значению, где возможны ties, а дубликаты должны попасть в один остров — берите DENSE_RANK(). Для уникальных дат разницы нет.

Разрыв на стыке месяца/года. Вычитать «число месяца» напрямую нельзя — 31 января и 1 февраля дадут разрыв. Приводите к абсолютной шкале: date - integer для дней или год*12 + месяц для месяцев.

Частые вопросы

В чём разница между gaps и islands?

Islands — непрерывные серии подряд идущих значений (это то, что обычно ищут: стрики, периоды активности). Gaps — разрывы между островами (например, «в какие даты пользователь пропускал заходы»). Оба находятся одним приёмом: если вы нашли острова с их границами, gaps — это промежутки между end одного острова и start следующего, посчитать их можно через LAG() по границам.

Почему именно ROW_NUMBER, а не RANK или DENSE_RANK?

Как посчитать текущий стрик (серию, которая длится прямо сейчас)?

Найдите все острова обычным способом, затем отфильтруйте тот, у которого MAX(log_date) равен сегодняшней дате (или вчерашней, если сегодня заход ещё не засчитан): HAVING MAX(log_date) = CURRENT_DATE. Длина этого острова и есть текущий стрик. Если такой строки нет — стрик прерван и равен нулю. Это частый доп-вопрос после основной задачи на собеседовании.

Работает ли этот приём в ClickHouse и других СУБД?

Да, идея универсальна — оконные функции ROW_NUMBER() OVER (...) есть в ClickHouse, MySQL 8+, Oracle, SQL Server. Различается только работа с датами: в PostgreSQL date - integer даёт дату, в других СУБД нужен dateadd/date_sub или приведение к числу дней. В ClickHouse удобно использовать toRelativeDayNum() для перевода даты в целое. Логика «вычесть номер строки и сгруппировать по разности» остаётся неизменной.

Что дальше

Gaps-and-islands — один из тех приёмов, где понимание важнее заучивания: осознали, почему разность фиксирована внутри серии — решите любую вариацию. На реальных собесах в продуктовые команды это спрашивают постоянно, часто с добавкой «а теперь самый длинный стрик» или «а текущий».

Потренируйтесь на живых задачах в /sql-sandbox — там 545 SQL-задач с автопроверкой, включая серии и оконные функции. Если оконные функции пока плывут — начните с бесплатного курса /course, где SQL разбирается с нуля. А перед самим собесом пройдитесь по /interviews с вопросами по SQL и гайду /sobesedovanie-analitika-dannyh, чтобы не растеряться на классике вроде этой.