Table Schema
Inspect
Table
| Column | Type | Description |
|---|---|---|
| user_id | integer | User identifier. |
| login_date | date | Date of the login. |
Consecutive
Login
Days
Find users who logged in for at least 2 consecutive days.
Inspect
Sample Data
Input
Output
Sample Input: logins
| user_id | login_date |
|---|---|
| 101 | 2024-01-01 |
| 101 | 2024-01-02 |
| 102 | 2024-01-01 |
| 102 | 2024-01-03 |
| 103 | 2024-01-04 |
| 103 | 2024-01-05 |
Expected Output
| user_id |
|---|
| 101 |
| 103 |
SQL Editor
Run
Query
Waiting for query
| user_id |
|---|
| 101 |
| 103 |
Hints
Unlock
Clues
Hint 01: Remove duplicate login rows before checking dates.
Hint 02: Join each login to the same user's next day login.
Hint 03: Keep users who have at least one consecutive pair.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
WITH distinct_logins AS (
SELECT DISTINCT
user_id,
login_date
FROM logins
)
SELECT DISTINCT
l1.user_id
FROM distinct_logins AS l1
JOIN distinct_logins AS l2
ON l1.user_id = l2.user_id
AND l1.login_date = l2.login_date - INTERVAL '1 day'
ORDER BY l1.user_id;
Explanation
Step By
Step
01
Remove duplicate login rows so the same day is only counted once.
02
Match each login to the same user's login from the next day.
03
Return users who have at least one consecutive-day pair.
Related Questions