Вопрос с собеседования · Аналитика данных

Как найти самую длинную серию дней подряд, когда пользователь был активен?

MiddleSQL для аналитикаSQL
Ещё спрашивают так:
  • Что такое задача gaps and islands?
Коротко

Классический приём «gaps and islands»: пронумеровать даты активности пользователя ROW_NUMBER и вычесть номер из даты — у дней одной непрерывной серии разность одинакова. Затем группировать по (пользователь, эта разность) и считать длину серии.

  • Сначала убрать дубли — одна строка на пользователя и день
  • Работает для любых последовательностей с шагом 1: дни, номера, часы
  • Альтернатива — LAG: отметить начало новой серии, накопительной суммой получить номер серии

А ещё Тот же приём находит периоды простоя, разрывы в номерах документов

Подробно

Почему работает. Если даты идут подряд (1, 2, 3 сентября), их номера тоже идут подряд (1, 2, 3), и разность постоянна. На пропуске дата перескакивает дальше номера — разность меняется, начинается новый «остров».

Вариант через LAG:

SELECT user_id, d,
       SUM(CASE WHEN d - LAG(d) OVER w = 1 THEN 0 ELSE 1 END) OVER w AS island
FROM days
WINDOW w AS (PARTITION BY user_id ORDER BY d);

Флаг «новая серия» там, где разрыв больше одного дня, а нарастающая сумма флагов даёт номер серии. Этот вариант легче обобщить на разрывы «больше 30 минут» (сессии).

Пример

WITH days AS (
  SELECT DISTINCT user_id, event_time::date AS d FROM events
),
grp AS (
  SELECT user_id, d,
         d - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d))::int AS island
  FROM days
)
SELECT user_id, MIN(d) AS start_day, MAX(d) AS end_day, COUNT(*) AS streak
FROM grp
GROUP BY user_id, island
ORDER BY streak DESC;

Что хотят услышать

  • Приём «дата минус номер».
  • Дедупликация дней.
  • Вариант через LAG и нарастающую сумму.

Проверьте себя

Один верный вариант. Подумайте, потом откройте ответ.

Показать варианты и ответ
  • ✓Дата минус ROW_NUMBER одинакова внутри серии — группировать по ней
  • ✗Найти MAX и MIN даты активности пользователя: их разность в днях плюс один и есть самая длинная серия
  • ✗Дубли дат удалять не нужно: ROW_NUMBER сам пропустит повторяющиеся дни
  • ✗Задача решается только циклом в Python: на чистом SQL серии дней не выделить

Связанные вопросы

Партнёр платформы

Разбор собеседования с sotto

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

Разбор собеседования