Task
Facebook wants to measure habit strength by finding each user's longest streak of consecutive daily activity. Write a SQL query to return the longest daily activity streak per user.
Requirements
- Treat multiple events by the same user on the same calendar date as a single active day.
- A streak is a sequence of activity dates where each date is exactly 1 day after the previous active date.
- Return one row per user with their
user_id, longest streak length, streak start date, and streak end date.
- If a user has multiple streaks with the same maximum length, return the earliest streak.
- Order the final output by
user_id ascending.
Table Definitions
facebook_activity
| column_name | type | description |
|---|
| activity_id | INT | Unique event identifier |
| user_id | INT | Facebook user identifier |
| activity_date | TIMESTAMP | Timestamp of the activity event |
| surface | VARCHAR(50) | Meta surface where the event happened |
| activity_type | VARCHAR(50) | Type of user action |
| device_type | VARCHAR(20) | Device used for the event |
Sample Data
| activity_id | user_id | activity_date | surface | activity_type | device_type |
|---|
| 8 | 102 | 2024-01-03 08:00:00 | Facebook Feed | view | Android |
| 1 | 101 | 2024-01-01 09:15:00 | Facebook Feed | like | iPhone |
| 3 | 101 | 2024-01-02 18:20:00 | Facebook Stories | comment | iPhone |
| 11 | 103 | 2024-01-10 09:00:00 | Facebook Feed | post | Web |
| 5 | 101 | 2024-01-05 11:00:00 | Facebook Feed | share | Android |
| 14 | 104 | 2024-01-04 16:00:00 | Facebook Feed | like | iPhone |
Expected Output
| user_id | longest_streak | streak_start_date | streak_end_date |
|---|
| 101 | 3 | 2024-01-01 | 2024-01-03 |
| 102 | 4 | 2024-01-03 | 2024-01-06 |
| 103 | 2 | 2024-01-10 | 2024-01-11 |
| 104 | 1 | 2024-01-04 | 2024-01-04 |
