Three-day login streaks
Hardsqlwindow functionsgaps and islandsdates
logins records every time a user signed in (possibly several times a day). Return the user_id of every user who signed in on at least three consecutive days at some point, sorted.
Tables
- logins (user_id INTEGER, day TEXT)
Example 1
logins
| user_id | day |
|---|---|
| 1 | 2026-01-01 |
| 1 | 2026-01-02 |
| 1 | 2026-01-02 |
| 1 | 2026-01-03 |
| 2 | 2026-01-01 |
| 2 | 2026-01-03 |
| 2 | 2026-01-04 |
Expected result
| user_id |
|---|
| 1 |
User 2 missed the 2nd.
Constraints
- 0 ≤ rows ≤ 2000
- day is YYYY-MM-DD
- The order of rows matters
Submit checks your solution against 10 tests, including hidden ones.
SQL
-- Write one SELECT query. Tables: logins SELECT