Longest Consecutive Login Streak
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a Logins table recording user logins. A user may have logged in multiple times on the same date - these count as a single login day. Write a SQL query to find the longest consecutive calendar day streak for each user. Return (user_id, longest_streak) ordered by user_id. Table: Logins
| Column Name | Type | Description |
|---|---|---|
| user_id | INT | ID of the user |
| login_date | DATE | Date of login |
Database Schema (Inferred)
Logins
| Column Name | Example Value |
|---|---|
| user_id | 1 |
| login_date | 2024-01-01 |
Example
Logins
| user_id | login_date |
|---|---|
| 1 | 2024-01-01 |
| 1 | 2024-01-02 |
| 1 | 2024-01-03 |
| 1 | 2024-01-05 |
| 1 | 2024-01-06 |
| 1 | 2024-01-07 |
| 2 | 2024-01-01 |
| 2 | 2024-01-03 |
| 2 | 2024-01-04 |
| 2 | 2024-01-05 |
Output
| user_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 3 |
Explanation:
User 1 has two streaks: Jan 1-3 (length 3) and Jan 5-7 (length 3). User 2 has streaks: Jan 1 (length 1) and Jan 3-5 (length 3). Deduplicate dates first, then use ROW_NUMBER minus date offset to identify consecutive groups.
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.
Logins
| user_id | login_date |
|---|---|
| 1 | 2024-01-01 |
| 1 | 2024-01-02 |
| 1 | 2024-01-03 |
| 1 | 2024-01-05 |
| 1 | 2024-01-06 |
| 1 | 2024-01-07 |
| 2 | 2024-01-01 |
| 2 | 2024-01-03 |
| 2 | 2024-01-04 |
| 2 | 2024-01-05 |
Output
| user_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 3 |