Longest Consecutive Login Streak per User
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a table of user login events. A user may log in multiple times on the same day. Write a SQL query to find the longest consecutive-day login streak for each user. Two logins are consecutive if one occurs exactly the day after the other. Duplicate login dates count as a single active day. Return one row per user with their longest streak length, ordered by user_id. Table: UserLogins
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| user_id | INT | ID of the user |
| login_date | DATE | Date of the login event |
Database Schema (Inferred)
UserLogins
| Column Name | Example Value |
|---|---|
| id | 1 |
| user_id | 1 |
| login_date | 2023-01-01 |
Example
UserLogins
| id | user_id | login_date |
|---|---|---|
| 1 | 1 | 2023-01-01 |
| 2 | 1 | 2023-01-02 |
| 3 | 1 | 2023-01-03 |
| 4 | 1 | 2023-01-05 |
| 5 | 1 | 2023-01-06 |
| 6 | 2 | 2023-01-01 |
| 7 | 2 | 2023-01-02 |
| 8 | 2 | 2023-01-04 |
| 9 | 3 | 2023-03-10 |
Output
| user_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
Explanation:
User 1 has streaks of 3 (Jan 1-3) and 2 (Jan 5-6); longest = 3. User 2 has a streak of 2 (Jan 1-2) then a gap; longest = 2. User 3 logged in once; streak = 1. The island-detection trick: subtract row_number from date to produce a constant group key for each consecutive run.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
UserLogins
| id | user_id | login_date |
|---|---|---|
| 1 | 1 | 2023-01-01 |
| 2 | 1 | 2023-01-02 |
| 3 | 1 | 2023-01-03 |
| 4 | 1 | 2023-01-05 |
| 5 | 1 | 2023-01-06 |
| 6 | 2 | 2023-01-01 |
| 7 | 2 | 2023-01-02 |
| 8 | 2 | 2023-01-04 |
| 9 | 3 | 2023-03-10 |
Output
| user_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |