Chime’s Product Growth team wants to measure short login streaks in the Chime app. Write a SQL query to find how many users logged in on exactly three consecutive calendar days.
A user should count only if they have at least one run of 3 consecutive distinct login dates, and that run is not part of a longer streak. Multiple logins on the same day should count as one login day.
chime_logins
| column_name | type | description |
|---|---|---|
| login_id | INT | Unique login event ID |
| user_id | INT | Chime user identifier |
| login_ts | TIMESTAMP | Timestamp when the user logged into the Chime app |
| device_type | VARCHAR(20) | Device used for the login |
| surface_name | VARCHAR(50) | Chime surface where the login occurred |
| login_id | user_id | login_ts | device_type | surface_name |
|---|---|---|---|---|
| 1 | 104 | 2024-01-10 08:00:00 | iOS | Chime App |
| 2 | 101 | 2024-01-03 09:00:00 | iOS | Chime App |
| 3 | 103 | 2024-01-05 08:00:00 | Android | Chime App |
| 4 | 102 | 2024-01-01 07:10:00 | Android | Chime App |
| 5 | 101 | 2024-01-01 08:15:00 | iOS | Chime App |
| 6 | 101 | 2024-01-02 12:30:00 | Web | Chime App |
| 7 | 102 | 2024-01-02 08:00:00 | Android | Chime App |
| 8 | 102 | 2024-01-03 09:30:00 | iOS | Chime App |
| 9 | 102 | 2024-01-04 10:00:00 | iOS | Chime App |
| 10 | 105 | 2024-01-07 11:00:00 | Web | Chime App |
| exact_three_day_users |
|---|
| 3 |