Longest consecutive login streak

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.

Streaks are a popular metric: 'you have logged in 7 days in a row'. Here you will find the longest streak of consecutive calendar days for each user. From logins, for each user, find the longest run of days where they logged in on every day, with no gaps. Return user_id and max_streak_length. Sort by max_streak_length from the longest, then by user_id.

Requirements

• Find runs of consecutive calendar days for each user.
• A gap of even 1 day ends the run.
• Return each user's longest run, as a number of days.
• A user with no consecutive days has a streak of 1.
• Sort by max_streak_length descending, then user_id.

Constraints

• The table is logins with the columns user_id and login_date.
• Use the 'islands' trick: subtract the row number from the date. Days in the same streak give the same result.
• Return only these 2 columns, in this order: user_id, max_streak_length.

Examples

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