0Pricing
Coding Interview Prep · Урок

Острова при изменении даты и статуса

Группируйте последовательные периоды с одинаковым статусом — это частый вопрос о состоянии подписки.

«Острова при изменении даты и статуса» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Острова, определяемые изменением значения

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

Здесь соседство не означает «значения отличаются на 1». Оно означает, что статус не изменился по сравнению с предыдущей строкой. Новый остров начинается в тот момент, когда статус меняется. Именно здесь подход на основе LAG лучше простого приёма с разностью номеров строк.

Пример данных о подписке

Рассмотрим таблицу sub_events для одного пользователя, упорядоченную по дате:

  • 2026-01-01 — активен
  • 2026-02-01 — активен
  • 2026-03-01 — приостановлен
  • 2026-04-01 — активен
  • 2026-05-01 — активен

В результате должны получиться три периода статуса: активен в январе–феврале, приостановлен в марте, активен в апреле–мае. Обратите внимание: два периода со статусом «активен» — это разные острова, поскольку между ними находится период приостановки. Одинаковый статус, не идущий подряд, означает разные острова.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Отмечаем изменения статуса

Используйте LAG, чтобы сравнить статус каждой строки с предыдущим. Если статусы отличаются (или предыдущее значение равно NULL для первой строки), начинается новый остров. Для изменения выведите 1, а в остальных случаях — 0.

Внутри пользователя выполняйте строгую сортировку по дате. Для наших данных флаги изменений имеют вид 1,0,1,1,0 и обозначают границы трёх периодов.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Преобразование накопительной суммы в ключ периода

Как и раньше, накопительная сумма флагов изменений даёт ключ группы, постоянный внутри каждого периода статуса: для наших строк это 1,1,2,3,3. Каждый отдельный ключ соответствует одному непрерывному периоду.

Приём с разностью номера строки здесь не сработает, поскольку статус не является числом, увеличивающимся на 1; рецепт с LAG и накопительной суммой — правильный инструмент, когда соседство означает «значение не изменилось».

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Сведение к периодам статуса

Теперь выполните GROUP BY по user_id, status и ключу накопительной суммы, чтобы вывести диапазон каждого периода. Включать статус в GROUP BY безопасно, поскольку внутри периода он постоянен; кроме того, это позволяет выбрать его без агрегатной функции.

В результате получаются ровно три строки: активен с 01-01 по 02-01, приостановлен с 03-01 по 03-01, активен с 04-01 по 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

От событий к полуоткрытым интервалам

Тонкость на собеседовании: дата события отмечает, когда статус начался, а период действительно заканчивается, когда начинается следующий статус, а не в дату последнего события с тем же статусом. Правильным концом периода часто служит начало следующего периода; это моделируется как полуоткрытый интервал [начало, следующее_начало).

Вычислите начало следующего периода с помощью LEAD для свёрнутых периодов, оставив последний период незакрытым (NULL или «текущий»).

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Обработка повторяющихся статусов подряд

Что делать, если в журнале есть избыточные строки вроде «активен, активен, активен», между которыми ничего не меняется? Для повторов флаг изменения равен 0, поэтому накопительная сумма автоматически оставляет их в одном острове. Именно этого и требуется добиться: идущие подряд одинаковые статусы сворачиваются в один период.

Такое естественное удаление повторов — ключевое преимущество метода с флагом изменения, и об этом стоит отдельно сказать интервьюеру.

Когда временные разрывы должны прерывать период

Иногда одного условия «статус тот же» недостаточно: большой временной разрыв должен также прерывать период, даже если статус не изменился. Например, активность в январе, а затем снова активность после шестимесячного перерыва может считаться двумя периодами.

Расширьте флаг изменения вторым условием: начинайте новый остров, когда статус меняется или время с момента предыдущего события превышает порог. Так оба правила соседства аккуратно объединяются.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Подсчёт уникальных переключений состояния

Естественный дополнительный вопрос: «Сколько раз этот пользователь переключал статус?» Это просто количество флагов изменения минус первый флаг (он отмечает начальное состояние, а не переключение).

Иными словами, это количество периодов минус 1. Ключ, созданный накопительной суммой, уже содержит эту информацию, поэтому ответ получается из того же механизма, который Вы построили для периодов.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Почему здесь это лучше соединения таблицы с самой собой

Решение с соединением таблицы с самой собой потребовало бы сопоставить каждую строку с соседней, обнаружить изменения, а затем объединить границы — это подверженная ошибкам многоэтапная процедура, которая с трудом справляется с тремя и более периодами.

Конвейер LAG–флаг–накопительная сумма–GROUP BY обрабатывает любое количество периодов за один проход и без соединений. Умение сформулировать это различие — линейный однопроходный подход по сравнению с квадратичным соединением таблицы с самой собой — как раз демонстрирует зрелое мышление, которое ценят интервьюеры.

Универсальный шаблон

Запомните этот шаблон из четырёх пунктов: он решает весь класс задач об островах статусов — меняется только проверка соседства в CASE:

  1. флаг: CASE с LAG для обнаружения нового острова.
  2. ключ: накопительная SUM флага с разбиением и сортировкой.
  3. свёртка: GROUP BY по столбцу разбиения, статусу и ключу.
  4. интервал (необязательно): LEAD для определения концов полуоткрытых периодов.

Та же схема подходит для последовательных целых чисел, дат и статусов; изменяется только условие CASE.

Быстрая проверка

Убедитесь, что Вы поняли правило группировки островов статусов.

Итоги: острова статусов и дат

Теперь Вы умеете решать наиболее содержательный вариант задачи о разрывах и островах:

  • Соседство означает, что статус не изменился по сравнению с предыдущей строкой; флаг меняется с помощью LAG.
  • Превратите флаги изменений накопительной суммой в ключ группы для каждого периода.
  • Сверните данные с помощью GROUP BY user_id, status, key, чтобы получить границы периодов.
  • Используйте LEAD для концов полуоткрытых интервалов; расширьте флаг, чтобы прерывать период при больших временных разрывах.
  • Повторяющиеся одинаковые строки сворачиваются автоматически; количество переключений получается из тех же флагов.
  • Один универсальный шаблон охватывает целые числа, даты и статусы — изменяется только CASE.

На этом завершается курс по разрывам и островам — надёжный показатель уровня опытного специалиста на собеседованиях по SQL.

Часто задаваемые вопросы

Урок «Острова при изменении даты и статуса» бесплатный?

Да — полный текст урока «Острова при изменении даты и статуса» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Острова при изменении даты и статуса»?

Группируйте последовательные периоды с одинаковым статусом — это частый вопрос о состоянии подписки. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Острова при изменении даты и статуса»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Распознавание задачи о пропусках и островах
  2. Приём с разностью номеров строк
  3. Поиск пропусков в последовательности
  4. Острова при изменении даты и статуса
← Назад к Coding Interview Prep