Найдите для каждого пользователя самую длинную серию дней подряд, когда он заходил в приложение
Проверила команда Сопровод · Обновлено
Короткий ответ
Сначала оставьте по одной строке на пользователя и день через DISTINCT. Затем вычтите из даты номер строки ROW_NUMBER() внутри пользователя: у дней подряд разность одинаковая, это и есть номер серии. Сгруппируйте по пользователю и этой разности, посчитайте дни в каждой серии и возьмите MAX.
Почему этот вопрос задают
Серии дней подряд — классика продвинутого уровня: так считают рекорды в трекерах, непрерывную работу датчиков и периоды подписки. Интервьюер смотрит, решите ли вы такую задачу одним запросом с оконной функцией, без циклов в коде.
Условие задачи
Трекер привычек пишет каждый заход в таблицу visits(user_id, visit_date). Если человек открыл приложение утром и вечером, в таблице две строки за один день. Команда хочет показать в профиле рекорд: самую длинную серию дней подряд с заходами. Верните user_id и длину серии, отсортируйте по user_id.
Как подойти к решению
- Уберите повторы: одна строка на пользователя и день через
DISTINCT - Пронумеруйте дни каждого пользователя через
ROW_NUMBER()по возрастанию даты - Вычтите номер из даты: у дней одной серии разность совпадает
- Сгруппируйте по пользователю и разности, посчитайте длину и возьмите максимум
Решение
ROW_NUMBER() возвращает bigint, а оператора date - bigint в PostgreSQL нет, поэтому номер приведен к int. Без DISTINCT повтор 2 сентября сдвигает нумерацию: у пользователя 1 разные серии склеятся в 4 дня, у пользователя 2 серия распадется на 2 и 3. Сложность определяет сортировка внутри окна, O(n log n).
WITH days AS (
SELECT DISTINCT user_id, visit_date FROM visits
),
islands AS (
SELECT user_id,
visit_date - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY visit_date)::int AS grp
FROM days
)
SELECT user_id, MAX(streak) AS longest_streak
FROM (
SELECT user_id, grp, COUNT(*) AS streak
FROM islands
GROUP BY user_id, grp
) s
GROUP BY user_id
ORDER BY user_id; user_id | longest_streak
---------+----------------
1 | 3
2 | 4
3 | 1
(3 rows)Что спросят следом
Как вывести еще даты начала и конца самой длинной серии?
Как решить задачу через
LAGвместо разности?Как посчитать текущую серию, которая еще не прервалась?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«
DISTINCTне нужен,ROW_NUMBER()и так пронумерует строки»Пронумерует, но повтор дня сдвинет номера, и одна серия распадется на две. Сначала сверните заходы до дней, потом нумеруйте
«Возьму
RANK(), он сам учтет одинаковые даты»RANK()даст повторам один номер и пропустит следующий, разность снова поедет. СDENSE_RANK()острова выйдут верными, но длину придется считать черезCOUNT(DISTINCT visit_date)«Соединю таблицу саму с собой по
visit_date + 1»Так вы найдете пары соседних дней, но длину серии придется собирать рекурсией или цепочкой соединений. Разность даты и номера строки дает ответ за один проход окна
«Если заходы хранятся со временем, вычту номер прямо из него»
Сначала приведите время к дате через
::date, иначеDISTINCTне склеит утренний и вечерний заходы. Да и вычесть число изtimestampPostgreSQL не даст: ошибкаoperator does not exist
Сигналы для интервьюера
Сильный ответ
Еще до кода спрашивает, бывают ли несколько заходов в день и в каком часовом поясе считать дату
Тревожный сигнал
Знает прием с разностью, но не может объяснить на двух-трех датах, почему у серии разность одинаковая
Красный флаг
Считает серией любые заходы за одну неделю и не уточняет у интервьюера, что значит «подряд»
Как еще могут спросить
- Как найти самую длинную последовательность дней подряд для каждого пользователя?
- Решите задачу gaps and islands: найдите непрерывные серии заходов пользователя
- Как посчитать максимальную серию ежедневных визитов запросом к таблице посещений?
- Как сгруппировать даты, идущие подряд, в отдельные периоды?











