SQL interview question: Longest Consecutive Login Streak
Daily Active User Streaks
The growth team wants to reward users who have maintained consecutive days of activity.
Find each user's longest streak of consecutive active days. Return user_id and max_streak_days.
Order by max_streak_days descending. This is a classic "gaps and islands" problem.
Inspired by Meta/Facebook's active user retention challenges
Tables
user_activity 18 rows
- activity_id integer PK
- user_id integer
- activity_date text
- activity_type text
| activity_id | user_id | activity_date | activity_type |
|---|---|---|---|
| 1 | 1 | 2025-01-01 | login |
| 2 | 1 | 2025-01-02 | login |
| 3 | 1 | 2025-01-03 | login |