Consecutive Login Streaks — Solution & ExplanationProblems / Consecutive Login Streaks / EditorialApproachThis is the classic islands and gaps problem. The goal is to identify maximal sequences of consecutive dates.Row Number Subtraction Trick (SQL)For consecutive dates, subtracting a sequential row number produces the same value:| login_date | ROW_NUMBER | date - rn ||-----------|-----------|----------|| Jan 1 | 1 | Dec 31 || Jan 2 | 2 | Dec 31 || Jan 3 | 3 | Dec 31 || Jan 6 | 4 | Jan 2 || Jan 7 | 5 | Jan 2 |Grouping by (user_id, date - row_number) identifies each island.Boundary Detection Approach (PySpark)

Use LAG to get the previous login date.

Mark a new streak where the gap exceeds 1 day.

Cumulative SUM of the boundary flags assigns streak IDs.

GROUP BY streak ID to get boundaries and length.

Complexity

Time: O(n log n) for sorting within each partition

Space: O(n) for the window buffer

Hints

The classic islands trick: subtract ROW_NUMBER() (as days) from each date. Consecutive dates produce the same group key.

An alternative approach uses LAG to detect streak boundaries, then cumulative SUM to assign group IDs.

After grouping, MIN/MAX give the streak boundaries and COUNT gives the length.

Solve Consecutive Login Streaks yourself →