SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.
From logins, find each user's longest streak of consecutive calendar days. Return user_id and max_streak_length. Order by max_streak_length descending, then user_id.
Input: logins user_id | login_date U1 | 2024-01-01 U1 | 2024-01-02 U1 | 2024-01-03 U1 | 2024-01-05 U2 | 2024-01-01 U2 | 2024-01-03 Output: user_id | max_streak_length U1 | 3 U2 | 1 Why this passes: U1 islands yield streaks of length 3 and 1; the max is 3. U2 has no consecutive days.
Topics: lakebench, sql, gaps and islands, streaks.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Gaps-and-islands streak length per user.
From `logins`, find each user's longest streak of consecutive calendar days. Return `user_id` and `max_streak_length`. Order by `max_streak_length` descending, then `user_id`.