Users With 3 or More Consecutive Login Days
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a table of user login records. Each row records a user ID and the date they logged in. A user may have duplicate entries for the same date. Write a SQL query to find all user IDs who have logged in on at least 3 consecutive calendar days at any point. Duplicate login dates for the same user should be treated as a single login for that day. Return the result ordered by user_id. Table: Logins
| Column Name | Type | Description |
|---|---|---|
| user_id | INT | ID of the user |
| login_date | DATE | Date the user logged in |
Database Schema (Inferred)
Logins
| Column Name | Example Value |
|---|---|
| user_id | 1 |
| login_date | 2023-01-01 |
Example
Logins
| user_id | login_date |
|---|---|
| 1 | 2023-01-01 |
| 1 | 2023-01-02 |
| 1 | 2023-01-03 |
| 2 | 2023-01-01 |
| 2 | 2023-01-03 |
| 3 | 2023-02-10 |
| 3 | 2023-02-11 |
| 3 | 2023-02-12 |
| 3 | 2023-02-13 |
Output
| user_id |
|---|
| 1 |
| 3 |
Explanation:
User 1 logged in on Jan 1, 2, 3 - 3 consecutive days. User 2 skipped Jan 2. User 3 logged in on 4 consecutive days. Users 1 and 3 qualify.
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 | 2023-01-01 |
| 1 | 2023-01-02 |
| 1 | 2023-01-03 |
| 2 | 2023-01-01 |
| 2 | 2023-01-03 |
| 3 | 2023-02-10 |
| 3 | 2023-02-11 |
| 3 | 2023-02-12 |
| 3 | 2023-02-13 |
Output
| user_id |
|---|
| 1 |
| 3 |