Task
Chime’s Product Growth team wants to identify members who were active on three consecutive calendar days in the Chime app. Write a SQL query to find users who completed at least one 3-day activity streak.
Use the app_events table, where each row is an in-app event. A user is considered active on a day if they generated at least one event on that date.
Requirements
- Deduplicate multiple events from the same user on the same day.
- Treat consecutive activity as calendar-day continuity (
2024-03-01, 2024-03-02, 2024-03-03).
- Return one row per qualifying streak, including the
user_id, streak_start_date, streak_end_date, and active_days_in_streak.
- Only return streaks with exactly 3 consecutive days in the detected window.
- Sort the final output by
streak_start_date, then user_id.
Table Definition
| column_name | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | Chime user identifier |
| event_name | VARCHAR(50) | Name of the in-app event |
| event_ts | TIMESTAMP | Timestamp when the event occurred |
| device_type | VARCHAR(20) | Device used for the event |
| campaign_name | VARCHAR(50) | Acquisition or lifecycle campaign tied to the event, if any |
Sample Data
| event_id | user_id | event_name | event_ts | device_type | campaign_name |
|---|
| 1003 | 101 | home_view | 2024-03-03 08:20:00 | android | payday_push |
| 1001 | 101 | login | 2024-03-01 09:00:00 | ios | referral_bonus |
| 1002 | 101 | card_settings_view | 2024-03-02 10:15:00 | ios | referral_bonus |
| 1004 | 101 | transfer_complete | 2024-03-03 18:45:00 | android | payday_push |
| 1005 | 102 | login | 2024-03-01 07:30:00 | ios | winback_email |
| 1006 | 102 | home_view | 2024-03-03 11:00:00 | ios | winback_email |
| 1007 | 103 | login | 2024-03-02 09:10:00 | android | null |
| 1008 | 103 | home_view | 2024-03-03 09:20:00 | android | null |
| 1009 | 103 | transfer_complete | 2024-03-04 20:00:00 | web | direct |
| 1010 | 104 | login | 2024-03-05 08:00:00 | web | paid_search |
Expected Output
| user_id | streak_start_date | streak_end_date | active_days_in_streak |
|---|
| 101 | 2024-03-01 | 2024-03-03 | 3 |
| 103 | 2024-03-02 | 2024-03-04 | 3 |
| 106 | 2024-03-08 | 2024-03-10 | 3 |
