Solving tips
- Gaps-and-islands trick: subtract a per-user ROW_NUMBER() (ordered by date) from the date itself — consecutive dates yield a constant anchor that labels each island.
- In PostgreSQL `date - integer` returns a date, so the row-number must be cast to int; the resulting anchor is constant only while dates increase by exactly one day.
- Count rows per (user, island) to get each streak length, then take MAX per user for the longest.
Each row records a day a user logged in (dates are distinct per user). Find each user’s longest run of consecutive calendar days.
Schema
CREATE TABLE logins (
user_id INTEGER,
login_date DATE
);
Sample data:
| user_id | login_date |
|---|
| 1 | 2024-01-01 |
| 1 | 2024-01-02 |
| 1 | 2024-01-03 |
| 1 | 2024-01-05 |
| 1 | 2024-01-06 |
| 2 | 2024-01-10 |
| 2 | 2024-01-11 |
| 2 | 2024-01-13 |
Task
Return one row per user with columns user_id and longest_streak (the number of days in that user’s longest run of consecutive dates). Order by user_id ascending.
Expected output
| user_id | longest_streak |
|---|
| 1 | 3 |
| 2 | 2 |
Approach
This is the classic gaps-and-islands pattern. Within a user, number the sorted login dates with ROW_NUMBER(). For a run of consecutive days, the date and the row number both increase by one each step, so login_date - row_number stays constant across the whole run and changes only when a gap breaks the sequence. That constant anchors each island; counting rows per island gives streak lengths, and the per-user maximum is the answer.
Query
WITH numbered AS (
SELECT
user_id,
login_date,
login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
FROM logins
),
islands AS (
SELECT user_id, grp, COUNT(*) AS streak_len
FROM numbered
GROUP BY user_id, grp
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;
Walkthrough
For user 1, the sorted dates get row numbers 1–5. The first three (01-01, 01-02, 01-03) subtract row numbers 1, 2, 3 to give the same anchor 2023-12-31; the gap before 01-05 shifts its anchor to 2024-01-01, shared by 01-06. So user 1 has two islands of sizes 3 and 2, and MAX returns 3. For user 2, 01-10 and 01-11 share anchor 2024-01-09 (size 2) while 01-13 sits alone (size 1), so MAX returns 2. ORDER BY user_id lists user 1 then user 2.
Complexity & notes
The window function sorts within each partition, O(n log n); the two grouping steps are linear. This works because sample dates are distinct per user — if a user could log in twice on one day, deduplicate first (SELECT DISTINCT user_id, login_date) or the row-number arithmetic breaks. An equivalent formulation uses date - INTERVAL comparisons or LAG to flag gap boundaries and a running SUM of the flags as the group id; the row-number subtraction is terser. Dialect note: date - integer returning a date is PostgreSQL-specific. In MySQL use DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (...) DAY), and in SQL Server use DATEADD(day, -ROW_NUMBER() OVER (...), login_date).