Самая длинная серия подряд активных дней на пользователя
Таблица activity(user_id, activity_date) содержит не более одной строки на пользователя за день. Для каждого пользователя верните длину самой длинной серии подряд идущих календарных дней с активностью (один активный день — серия из 1).
-- на пользователя: длина самой длинной серии подряд активных днейНапишите запрос.
Приём gaps-and-islands: на пользователя вычитают из даты ROW_NUMBER() с сортировкой по дате — подряд идущие дни делят одно смещение, постоянный ключ острова. Группируют по пользователю и ключу, COUNT(*) дней в серии, затем MAX длины на пользователя.
- ✗Считать все строки день-за-днём вместо самой длинной одной серии
- ✗Брать интервал первый-последний, будто активность непрерывна
- ✗Считать все уникальные активные дни как длиннейшую серию
- →Почему
activity_date - ROW_NUMBER()постоянен внутри серии? - →Как ещё вернуть дату начала и конца этой серии?
The gaps-and-islands trick: within a run of consecutive dates, the date increases by one each row and so does an ordered ROW_NUMBER(), so date - row_number is constant — a stable id for that run. Each break in the dates shifts the offset, starting a new island:
WITH daily AS (
SELECT DISTINCT user_id, activity_date FROM activity
),
grouped AS (
SELECT user_id,
activity_date,
activity_date
- (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date))::int AS grp
FROM daily
),
runs AS (
SELECT user_id, grp, COUNT(*) AS streak
FROM grouped
GROUP BY user_id, grp
)
SELECT user_id, MAX(streak) AS longest_streak
FROM runs
GROUP BY user_id;(ROW_NUMBER() ...)::int subtracted from a date yields a date (date minus integer), and consecutive days collapse to one grp value. COUNT(*) per grp is that run's length; MAX per user is the longest. The DISTINCT guards against duplicate rows per day, which would otherwise break the one-row-per-day assumption the offset relies on.
