Gamified apps like language learners and fitness trackers reward users who return every single day. To power these incentives the engagement team needs to find each user's longest unbroken run of consecutive calendar days with at least one login. Using the logins table, return user_id and longest_streak (the maximum number of consecutive calendar days the user logged in), ordered by user_id.
logins
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| login_time | TIMESTAMP |
logins
| id | user_id | login_time |
|---|---|---|
| 1 | 1 | 2024-01-01 08:00:00 |
| 2 | 1 | 2024-01-02 09:00:00 |
| 3 | 1 | 2024-01-03 10:00:00 |
| 4 | 1 | 2024-01-05 08:00:00 |
| 5 | 2 | 2024-01-01 11:00:00 |
| 6 | 2 | 2024-01-02 12:00:00 |
| user_id | longest_streak |
|---|---|
| 1 | 3 |
| 2 | 2 |
User 1 logged in on Jan 1, 2, and 3 consecutively (streak = 3), then again on Jan 5 alone (streak = 1). User 2 logged in on Jan 1 and 2 consecutively (streak = 2).