Difficulty: Hard | Concepts: Gaps and Islands, ROW_NUMBER, DATE_SUB, GROUP BY, Window Functions

The SQL gaps and islands problem is one of the most common advanced SQL interview questions asked at product companies, because it tests whether you can turn a sequence of rows into meaningful groups. A frequent real-world version of it is finding consecutive login streaks in SQL — exactly what we'll solve here using window functions.

Consecutive Streaks in SQL — Problem Statement

You are given a table of daily user logins. For each user, identify every streak of consecutive login days and return the start date, end date, and length of each streak. This is also known as the gaps and islands problem in SQL.

Dataset

CREATE TABLE User_Logins
(
User_ID INT,
Login_Date DATE
);

INSERT INTO User_Logins VALUES
(1,'2026-06-01'),
(1,'2026-06-02'),
(1,'2026-06-03'),
(1,'2026-06-05'),
(1,'2026-06-06'),
(2,'2026-06-01'),
(2,'2026-06-02'),
(2,'2026-06-04'),
(2,'2026-06-05'),
(2,'2026-06-06');

Expected Output

User	Streak Start	Streak End	Streak Length
1	2026-06-01	2026-06-03	3
1	2026-06-05	2026-06-06	2
2	2026-06-01	2026-06-02	2
2	2026-06-04	2026-06-06	3

SQL Solution — Find Consecutive Date Ranges

The classic trick to identify streaks in SQL using window functions is to subtract a row number from the date. Consecutive dates minus an increasing row number always produce the same constant value, which becomes a natural grouping key:

WITH Ranked AS (
    SELECT
        User_ID,
        Login_Date,
        ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Login_Date) AS rn,
        DATE_SUB(Login_Date, INTERVAL ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Login_Date) DAY) AS streak_group
    FROM User_Logins
)
SELECT
    User_ID,
    MIN(Login_Date) AS Streak_Start,
    MAX(Login_Date) AS Streak_End,
    COUNT(*) AS Streak_Length
FROM Ranked
GROUP BY User_ID, streak_group
ORDER BY User_ID, Streak_Start;

Explanation

For each user, ROW_NUMBER() PARTITION BY User_ID ORDER BY Login_Date assigns a sequential number to every login date. When you subtract that row number (in days) from the actual login date, every date inside a consecutive run lands on the exact same "anchor" date — because both the date and the row number increase by exactly 1 each day. That shared anchor becomes the grouping key for GROUP BY, letting you collapse each island into a single row with MIN(), MAX(), and COUNT().

Visual Representation

User 1 login dates: Jun 01 Jun 02 Jun 03 [gap] Jun 05 Jun 06 rn=1 rn=2 rn=3 rn=4 rn=5 date - rn (days): May 31 May 31 May 31 Jun 01 Jun 01 \______island A______/ \__island B__/ Island A -> Jun 01 to Jun 03, length 3 Island B -> Jun 05 to Jun 06, length 2

Conclusion

The gaps and islands technique shown here isn't limited to login streaks — the same pattern solves finding consecutive order dates, detecting active subscription periods, identifying uninterrupted sensor readings, and spotting continuous attendance streaks. Once you understand the row-number-minus-date trick, you can apply this SQL window function pattern to almost any sequential-grouping interview question.