Как сделать Gap Analysis в SQL

Зачем Gap Analysis

«Gaps and Islands» («пропуски и островки») — классический SQL-паттерн. Задача одна: в упорядоченном ряду событий найти непрерывные последовательности (islands, островки) и разрывы между ними (gaps, пропуски). На этот паттерн раскладывается куча реальных задач — стрики в приложениях (как в Duolingo), периоды активности пользователя, пропуски в ежедневных метриках, дыры в последовательности ID. Если вы умеете считать островки, половина «странных» задач на собесе решается одним и тем же приёмом.

Gaps and Islands: базовый паттерн

Ключевой трюк — присвоить каждому островку общий идентификатор через разницу между датой и ROW_NUMBER(). Смысл в том, что для подряд идущих дат эта разница постоянна: дата растёт на 1 день, номер строки растёт на 1, и дата − номер не меняется. Как только в датах появляется пропуск, разница скачет — начинается новый островок.

WITH activity AS (
    SELECT
        user_id,
        active_date,
        active_date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date) AS streak_anchor
    FROM user_activity
)
SELECT
    user_id,
    streak_anchor,
    COUNT(*) AS streak_length,
    MIN(active_date) AS streak_start,
    MAX(active_date) AS streak_end
FROM activity
GROUP BY user_id, streak_anchor
ORDER BY streak_length DESC;

streak_anchor — это и есть идентификатор островка: у всех дат внутри одного стрика он одинаковый, поэтому по нему можно группировать и считать длину серии.

Пропущенные даты в ряду

Обратная задача — найти именно разрывы. Здесь удобнее LAG(): сравниваем каждую дату с предыдущей и ловим случаи, где между ними больше одного дня.

WITH ordered AS (
    SELECT
        DATE,
        LAG(DATE) OVER (ORDER BY DATE) AS prev_date,
        DATE - LAG(DATE) OVER (ORDER BY DATE) AS gap_days
    FROM daily_metrics
)
SELECT
    prev_date AS gap_start,
    DATE AS gap_end,
    gap_days - 1 AS missing_days
FROM ordered
WHERE gap_days > 1
ORDER BY gap_start;

Результат — список интервалов, где данных нет: с какого по какое число и сколько дней потеряно. Такой запрос часто ставят в основу алерта «в метриках пропал день».

Стрик активных дней

Соединяем два предыдущих приёма, чтобы посчитать по каждому пользователю максимальный стрик и текущий (тот, что заканчивается вчера):

WITH grouped AS (
    SELECT
        user_id,
        active_date,
        active_date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date) AS group_key
    FROM user_activity
),
streaks AS (
    SELECT
        user_id,
        group_key,
        COUNT(*) AS streak_length,
        MIN(active_date) AS start_date,
        MAX(active_date) AS end_date
    FROM grouped
    GROUP BY user_id, group_key
)
SELECT
    user_id,
    MAX(streak_length) AS longest_streak,
    MAX(streak_length) FILTER (WHERE end_date = CURRENT_DATE - 1) AS current_streak
FROM streaks
GROUP BY user_id
ORDER BY longest_streak DESC;

current_streak через FILTER берёт длину только того островка, который упирается во вчерашний день — если у пользователя последняя активность раньше, текущий стрик обнуляется.

Пропуски в последовательности ID

Тот же принцип работает не только для дат, но и для целочисленных ID. Ищем «дыры» в последовательности — например, пропавшие события:

WITH ordered AS (
    SELECT
        id,
        LAG(id) OVER (ORDER BY id) AS prev_id
    FROM events
)
SELECT
    prev_id AS missing_after,
    id AS missing_before,
    id - prev_id - 1 AS missing_count
FROM ordered
WHERE id - prev_id > 1
ORDER BY missing_count DESC;

Логика идентична поиску пропущенных дат — меняется только шаг (1 единица вместо 1 дня).

Островки по атрибуту

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

WITH status_changes AS (
    SELECT
        user_id,
        status_date,
        status,
        LAG(status) OVER (PARTITION BY user_id ORDER BY status_date) AS prev_status,
        SUM(CASE
            WHEN LAG(status) OVER (PARTITION BY user_id ORDER BY status_date) = status THEN 0
            ELSE 1
        END) OVER (PARTITION BY user_id ORDER BY status_date) AS island_id
    FROM user_status_history
)
SELECT
    user_id,
    status,
    island_id,
    MIN(status_date) AS island_start,
    MAX(status_date) AS island_end,
    COUNT(*) AS island_length_days
FROM status_changes
GROUP BY user_id, status, island_id;

Здесь island_id считается нарастающей суммой: она увеличивается на 1 каждый раз, когда статус отличается от предыдущего. Внутри одного непрерывного статуса значение не меняется — по нему и группируем.

Как это спрашивают на собесе

Gaps and islands — любимая задача SQL-секции, потому что проверяет владение оконными функциями на нетривиальном кейсе.

«Посчитай самый длинный стрик активных дней пользователя.» Ждут трюк дата − ROW_NUMBER(): для подряд идущих дат разница постоянна, по ней группируем и берём MAX(COUNT). Разобрать такие SQL-задачи с решением можно в Карьернике.

«Почему именно ROW_NUMBER(), а не RANK()?» Потому что при равных значениях RANK() даёт одинаковый номер и «ломает» разницу дата − номер. Для логики островков нужен строго уникальный сквозной номер — это ROW_NUMBER().

«Найди дни, когда в метриках нет данных.» Здесь ждут LAG() по дате и фильтр gap_days > 1 — это обратная сторона того же паттерна, поиск не островков, а пропусков.

«Как схлопнуть подряд идущие одинаковые статусы в периоды?» Нарастающая сумма флага «статус изменился» даёт идентификатор островка, по которому потом группируют. Классический вопрос на понимание, что островок можно задать не только датами.

Частые ошибки

• Путать ROW_NUMBER() и RANK(). При ties RANK() выдаёт одинаковые номера и ломает разницу дата − номер. Для логики островков нужен ROW_NUMBER().
• Смешивать типы при вычитании. date − integer и date − interval ведут себя по-разному. Приводите типы аккуратно, иначе получите либо ошибку, либо неверный результат.
• Забывать PARTITION BY. Без разбивки по пользователю оконная функция «перетекает» с одного пользователя на другого, и стрики склеиваются между людьми.
• Не обрабатывать крайние строки. У первой строки LAG() вернёт NULL — это нормально, но такой случай надо явно учитывать в фильтре, чтобы не потерять или не сломать первый интервал.
• Игнорировать смысл размера пропуска. Разрыв в 1 день и в 7 дней — это разные вещи. Порог, начиная с которого разрыв считается значимым, нужно задавать осознанно под задачу.

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

• Window functions advanced
• Как посчитать active cohort в SQL
• Как посчитать sessionization в SQL
• Cumulative distinct на собесе DE

FAQ

Что такое паттерн gaps and islands?

Это способ в упорядоченном ряду находить непрерывные последовательности (островки) и разрывы между ними (пропуски). На него раскладываются задачи про стрики, периоды активности, пропуски в датах и дыры в последовательностях ID — везде используется один и тот же приём группировки.

Почему ROW_NUMBER(), а не RANK()?

ROW_NUMBER() присваивает каждой строке уникальный номер, поэтому разница «дата − номер» корректно постоянна внутри островка. RANK() при равных значениях выдаёт одинаковый ранг, из-за чего эта разница ломается и группировка островков становится неверной.

Почему работает трюк «дата минус номер строки»?

Для подряд идущих дат дата растёт на 1 день, а номер строки — на 1, поэтому их разность не меняется и служит идентификатором островка. Как только между датами появляется пропуск, разность скачком меняется — начинается новый островок.

Паттерн работает только с датами?

Нет. Логика одинакова для дат и для целочисленных последовательностей (ID, порядковые номера) — меняется только шаг: 1 день для дат или 1 единица для чисел. Для островков по атрибуту вместо разницы используют нарастающую сумму флага «значение изменилось».

Где это реально применяется?

Стрики в приложениях (Duolingo и подобные), периоды непрерывной активности пользователей, алерты о пропусках данных в ежедневных метриках, поиск дыр во временных рядах и в последовательностях ID. Паттерн — базовый инструмент любого продуктового и DE-аналитика.