Альфа-Банк SQL — периоды действия сегментов клиента (gaps and islands)

Условие

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

create table #segment ([date] date, ClientID varchar(6), SegmentID int);
insert into #segment values
  ('2018-01-31','A11111',2), ('2018-02-28','A11111',2),
  ('2018-03-31','A11111',1), ('2018-04-30','A11111',1),
  ('2017-11-30','B22222',1), ('2017-10-31','B22222',1),
  ('2017-09-30','B22222',3),
  ('2017-09-30','C33333',1), ('2017-10-31','C33333',1);

Ожидаемый результат:

Решение

Подход

Это классическая задача gaps and islands. Идея «двух row_number»: один номер по последовательности всех строк клиента, второй — по строкам внутри одного сегмента. Их разность константна для каждой непрерывной серии одного сегмента.

Реализация

WITH numbered AS (
  SELECT
    ClientID, SegmentID, [date],
    ROW_NUMBER() OVER (PARTITION BY ClientID            ORDER BY [date]) AS rn_all,
    ROW_NUMBER() OVER (PARTITION BY ClientID, SegmentID ORDER BY [date]) AS rn_seg
  FROM #segment
)
SELECT
  ClientID,
  SegmentID,
  MIN([date]) AS start_date,
  MAX([date]) AS end_date
FROM numbered
GROUP BY ClientID, SegmentID, (rn_all - rn_seg)
ORDER BY ClientID, start_date;

Почему это работает

Для непрерывной серии одного и того же сегмента разность rn_all - rn_seg постоянна, потому что оба счётчика растут синхронно. Как только сегмент меняется, rn_seg сбрасывается, но rn_all продолжает расти — разность скачет, и группа «островов» меняется.

Альтернатива через LAG

WITH flagged AS (
  SELECT
    ClientID, SegmentID, [date],
    CASE WHEN LAG(SegmentID) OVER (PARTITION BY ClientID ORDER BY [date]) = SegmentID
         THEN 0 ELSE 1 END AS is_new_island
  FROM #segment
),
islands AS (
  SELECT
    ClientID, SegmentID, [date],
    SUM(is_new_island) OVER (PARTITION BY ClientID ORDER BY [date]) AS island_id
  FROM flagged
)
SELECT ClientID, SegmentID, MIN([date]) AS start_date, MAX([date]) AS end_date
FROM islands
GROUP BY ClientID, SegmentID, island_id
ORDER BY ClientID, start_date;

Подводные камни

1. Просто GROUP BY ClientID, SegmentID не работает: если сегмент 1 встречается дважды с перерывом, обе серии слипнутся в одну.
2. DENSE_RANK вместо ROW_NUMBER ломает разность — нужны именно номера строк.
3. Дыры в данных. Если месяц пропущен (нет среза), формально это всё ещё «один сегмент»? Зависит от бизнес-логики. Можно добавить условие на LAG([date]) — если разница больше месяца, начинаем новый остров.
4. Дубль строк. Если на одну дату по клиенту два сегмента, оконка выдаст странности. Обычно делают DISTINCT ClientID, [date], SegmentID или вычисляют доминирующий сегмент.

Эталонный ответ

Классический «gaps and islands» через двойной ROW_NUMBER или флаг смены через LAG. Группируем по (ClientID, SegmentID, island_id) и берём MIN/MAX(date).