Условие:
Есть таблица логинов пользователей:
CREATE TABLE user_logins (
user_id INT,
login_time TIMESTAMP
);
Нужно — для каждого пользователя найти его максимальную по длине цепочку непрерывных ежедневных входов.
Если он заходил 1, 2, 3, 5 и 6 января — самая длинная последовательность без пропусков: 3 дня (1–3 января).
Учитываются только уникальные дни входа (то есть даже если зашёл 3 раза в день — это один день).
Ожидаемый результат:
| user_id | start_date | end_date | chain_length |
|---------|------------|------------|--------------|
| 101 | 2024-01-01 | 2024-01-03 | 3 |
| 102 | 2024-03-05 | 2024-03-09 | 5 |Решение основано на классической технике: «группировка по смещению».
Шаги:
1. Убираем дубликаты по датам
2. Пронумеровываем строки по `ROW_NUMBER` для каждой даты входа
3. Вычисляем разницу между датой и порядковым номером — она будет **одинакова для цепочки подряд идущих дат**
4. Группируем по этой разности и ищем максимальную длину цепи
Решение (PostgreSQL):
```sql
WITH distinct_dates AS (
SELECT DISTINCT user_id, DATE(login_time) AS login_date
FROM user_logins
),
numbered AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM distinct_dates
),
grouped AS (
SELECT *,
login_date - INTERVAL '1 day' * rn AS grp
FROM numbered
),
grouped_ranges AS (
SELECT user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS chain_length
FROM grouped
GROUP BY user_id, grp
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY chain_length DESC) AS rnk
FROM grouped_ranges
) final
WHERE rnk = 1;
```
Как работает:
- `ROW_NUMBER` генерирует позицию в порядке дат
- Вычитание `login_date - rn` создаёт искусственный "ключ", по которому мы группируем
- Внутри группы — последовательные даты
- Выбираем цепочку с максимальной длиной по каждому `user_id`
Почему это задача с подвохом:
• Нельзя решить через простой
GROUP BY • Требуется умение работать с датами, оконными функциями и многоступенчатыми CTE
• Это реальный кейс из аналитики поведения пользователей, встречается в проде
➡ SQL Community