Given a logins table, write a single SQL query that returns, for each user, the length of their longest run of consecutive calendar days with at least one login.
Input
logins(user_id INTEGER, login_date DATE) — one row per login. A user may log in more than once on the same day.
Task
For each user, find the longest streak of consecutive calendar days on which they logged in. Multiple logins on the same day count as a single day. A streak is a maximal run of dates where each is exactly one day after the previous.
Output
One row per user, columns in this exact order:
user_id (INTEGER)longest_streak (INTEGER) — number of days in the user's longest consecutive run
Order by user_id ascending.
Evaluation
Your query is executed against several hidden datasets in a DuckDB sandbox. Output is compared row-for-row against the reference result.
Worked example
User 1 logs in on Jan 1, 2, 3, 4, 5, then Jan 8 and 9 — the longest consecutive run is Jan 1-5 (5 days). User 2 logs in on Jan 1 and Jan 3 only (never consecutive), so the longest streak is 1.
Expected output:
| user_id | longest_streak |
|---------|----------------|
| 1 | 5 |
| 2 | 1 |