Классический приём «gaps and islands»: пронумеровать даты активности пользователя ROW_NUMBER и вычесть номер из даты — у дней одной непрерывной серии разность одинакова. Затем группировать по (пользователь, эта разность) и считать длину серии.
А ещё Тот же приём находит периоды простоя, разрывы в номерах документов
Так подсказку видит пользователь sotto на живом собеседовании — за пару секунд, пока вы думаете вслух.
Почему работает. Если даты идут подряд (1, 2, 3 сентября), их номера тоже идут подряд (1, 2, 3), и разность постоянна. На пропуске дата перескакивает дальше номера — разность меняется, начинается новый «остров».
Вариант через LAG:
sottoSELECT 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;sotto слышит вопрос интервьюера и показывает короткий ответ и план, как в блоке «Коротко». Оставьте контакт — пришлём доступ и промокод.