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_iduser_idactivity_dateactivity_type
112025-01-01login
212025-01-02login
312025-01-03login
About the e-commerce sample database

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

Keep going