Хабр SQL — пользователи, активные 3 дня подряд

Условие

Таблица activity(user_id int, active_date date). Найдите всех пользователей, у которых есть хотя бы один отрезок из 3 последовательных дней активности подряд (например 2025-05-01, 2025-05-02, 2025-05-03).

Решение

Подход 1 — LAG на 2 строки

Если у пользователя active_date - LAG(active_date, 2) OVER (PARTITION BY user_id ORDER BY active_date) = 2 — значит, есть 3 подряд.

WITH dedup AS (
  SELECT DISTINCT user_id, active_date FROM activity
)
SELECT DISTINCT user_id
FROM (
  SELECT
    user_id,
    active_date,
    LAG(active_date, 2) OVER (PARTITION BY user_id ORDER BY active_date) AS prev2
  FROM dedup
) t
WHERE active_date - prev2 = 2;

Подход 2 — классический «gaps and islands»

Группируем по разности active_date и row_number (если последовательно, разность постоянна).

WITH dedup AS (SELECT DISTINCT user_id, active_date FROM activity),
labeled AS (
  SELECT
    user_id,
    active_date,
    active_date - INTERVAL '1 day' *
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date) AS grp
  FROM dedup
),
islands AS (
  SELECT user_id, grp, COUNT(*) AS streak_len
  FROM labeled
  GROUP BY user_id, grp
)
SELECT DISTINCT user_id
FROM islands
WHERE streak_len >= 3;

Вариант 2 универсальнее: легко поменять «3 подряд» на «N подряд» через streak_len >= N и сразу даёт длину серии.

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

1. Дубликаты дат. Если один и тот же active_date встречается дважды — ROW_NUMBER сместится. Сначала DISTINCT.
2. LAG(..., 2) возвращает NULL для первых двух строк — это ок, фильтр сравнения уберёт их.
3. Разные часовые пояса. Активность ночью в UTC может попасть «в чужой день» — нормализуйте до агрегации.
4. Streak vs cumulative streak. «Самый длинный streak» отдельно: MAX(streak_len) OVER (PARTITION BY user_id) поверх islands.
5. active_date - prev2 = 2 — арифметика на date в Postgres даёт integer, в MySQL — нужен DATEDIFF. В SQL Server — DATEDIFF(DAY, prev2, active_date) = 2.

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

«Gaps and islands»: active_date - ROW_NUMBER() OVER (...) создаёт стабильный ключ для всех элементов одной серии. Группа с COUNT(*) >= 3 — искомая серия.