Introduction

Have you ever needed to answer questions like these?

• How many consecutive days did a customer place an order?
• How many days did an employee log into the system without interruption?
• What was the longest attendance streak?
• How many consecutive days was a machine operational?
• How many days did a user maintain a GitHub contribution streak?

These problems are commonly known as Gaps and Islands problems.

Although they may seem complicated at first, SQL Server provides elegant solutions using Window Functions, making them both efficient and easy to understand.

In this article, you’ll learn one of the most popular techniques for solving consecutive-day problems.

What are Gaps and Islands?

A sequence of consecutive values is called an Island.

Missing values between consecutive records are called Gaps.

For example:

Date	Status
01-Jan	Login
02-Jan	Login
03-Jan	Login
05-Jan	Login
06-Jan	Login
09-Jan	Login

Here,

Island 1

• 01-Jan
• 02-Jan
• 03-Jan

Gap

• 04-Jan

Island 2

• 05-Jan
• 06-Jan

Gap

• 07-Jan
• 08-Jan

Island 3

• 09-Jan

Sample Data

CREATE TABLE EmployeeLogin
(
    EmployeeID INT,
    LoginDate DATE
);

INSERT INTO EmployeeLogin
VALUES
(101,'2025-01-01'),
(101,'2025-01-02'),
(101,'2025-01-03'),
(101,'2025-01-05'),
(101,'2025-01-06'),
(101,'2025-01-09');

Current data

EmployeeID	LoginDate
101	2025-01-01
101	2025-01-02
101	2025-01-03
101	2025-01-05
101	2025-01-06
101	2025-01-09

Step 1 – Generate Row Numbers

SELECT
    EmployeeID,
    LoginDate,
    ROW_NUMBER() OVER
    (
        PARTITION BY EmployeeID
        ORDER BY LoginDate
    ) AS RN
FROM EmployeeLogin;

Output

LoginDate	RN
01-Jan	1
02-Jan	2
03-Jan	3
05-Jan	4
06-Jan	5
09-Jan	6

The row number simply assigns a sequential number to each login date.

Step 2 – Create a Group Identifier

Now comes the trick.

Subtract the row number from the date.

SELECT
    EmployeeID,
    LoginDate,
    DATEADD(day,
            -ROW_NUMBER() OVER
            (
                PARTITION BY EmployeeID
                ORDER BY LoginDate
            ),
            LoginDate) AS GroupID
FROM EmployeeLogin;

Output

LoginDate	GroupID
01-Jan	2024-12-31
02-Jan	2024-12-31
03-Jan	2024-12-31
05-Jan	2025-01-01
06-Jan	2025-01-01
09-Jan	2025-01-03

Notice something interesting?

All consecutive dates produce the same GroupID.

That becomes our Island identifier.

Step 3 – Aggregate Each Island

Now simply group by the generated GroupID.

WITH LoginGroups AS
(
    SELECT
        EmployeeID,
        LoginDate,
        DATEADD(day,
            -ROW_NUMBER() OVER
            (
                PARTITION BY EmployeeID
                ORDER BY LoginDate
            ),
            LoginDate) AS GroupID
    FROM EmployeeLogin
)
SELECT
    EmployeeID,
    MIN(LoginDate) AS StartDate,
    MAX(LoginDate) AS EndDate,
    COUNT(*) AS ConsecutiveDays
FROM LoginGroups
GROUP BY
    EmployeeID,
    GroupID
ORDER BY StartDate;

Result

StartDate	EndDate	ConsecutiveDays
2025-01-01	2025-01-03	3
2025-01-05	2025-01-06	2
2025-01-09	2025-01-09	1

Finding the Longest Streak

Suppose we only want the longest streak.

WITH LoginGroups AS
(
    SELECT
        EmployeeID,
        LoginDate,
        DATEADD(day,
            -ROW_NUMBER() OVER
            (
                PARTITION BY EmployeeID
                ORDER BY LoginDate
            ),
            LoginDate) AS GroupID
    FROM EmployeeLogin
),
Streaks AS
(
    SELECT
        EmployeeID,
        MIN(LoginDate) AS StartDate,
        MAX(LoginDate) AS EndDate,
        COUNT(*) AS ConsecutiveDays
    FROM LoginGroups
    GROUP BY EmployeeID, GroupID
)
SELECT TOP (1) *
FROM Streaks
ORDER BY ConsecutiveDays DESC;

Result

EmployeeID	StartDate	EndDate	ConsecutiveDays
101	2025-01-01	2025-01-03	3

Why Does This Technique Work?

Suppose we have three consecutive dates:

LoginDate	Row Number
01-Jan	1
02-Jan	2
03-Jan	3

Subtracting the row number from each date gives:

LoginDate	LoginDate − RN
01-Jan	31-Dec
02-Jan	31-Dec
03-Jan	31-Dec

Since the result is identical for every consecutive date, SQL Server can group them together automatically.

Whenever a gap occurs, the calculated value changes, creating a new group.

This elegant property makes the solution both simple and efficient.

Real-World Use Cases

This technique is useful for:

• Employee attendance tracking
• User login streaks
• Website daily active users
• IoT sensor uptime analysis
• Machine maintenance monitoring
• Sales activity tracking
• Patient hospital visits
• Consecutive purchase analysis
• GitHub contribution streaks
• Azure SQL monitoring reports

Performance Considerations

For large tables:

• Create an index on (EmployeeID, LoginDate).
• Avoid unnecessary sorting by matching the index order with the window function.
• Use ROW_NUMBER() only after filtering the required data.
• For very large datasets, review the execution plan to ensure the window function isn’t causing expensive sorts or spills.

Conclusion

The Gaps and Islands technique is one of the most valuable SQL patterns for analyzing consecutive data. By combining ROW_NUMBER() with simple date arithmetic, you can efficiently identify streaks, calculate their lengths, and answer a wide variety of business questions.

Whether you’re analyzing customer activity, employee attendance, or application usage, this pattern is an essential addition to every SQL developer’s toolkit.