Consecutive Days, Sessions — Gaps and Islands

The pattern behind 'consecutive logins', 'session detection', 'streaks' — one of the SQL world's named techniques. FIND_VIDEO: search 'SQL gaps and islands consecutive days' — recommended channel: Itzik Ben-Gan / Modern SQL. Aim for 12 min or under.

4 minutesVideo LessonPDF notes

Watch Consecutive Days, Sessions — Gaps and Islands on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Introduction to Gaps and Islands — The playing card analogy illustrates sequential island groups and missing gap elements.
Date Range Overlap Complexities — Sample data highlights challenges with overlapping and fully enclosed interval records.
Lag Function and Row Numbering — Row numbering and the LAG function capture the previous interval boundary.
Island Indicator and Running Total — A conditional start indicator combined with a running SUM assigns group IDs.
Aggregation into Island Ranges — Grouping by island ID with MIN and MAX consolidates fragmented date ranges.

PDF notes

Bottom line: You will master solving advanced gaps and islands problems involving overlapping, touching, and fully enclosed date ranges using window functions. By the end of this lesson, you will be able to construct multi-stage queries using LAG, cumulative conditional sums, and aggregate grouping to merge fragmented time intervals into unified session ranges.

This lesson on Consecutive Days, Sessions — Gaps and Islands is hands-on and example-driven. You will master solving advanced gaps and islands problems involving overlapping, touching, and fully enclosed date ranges using window functions. By the end of this lesson, you will be able to construct multi-stage queries using LAG, cumulative conditional sums, and aggregate grouping to merge fragmented time intervals into unified session ranges.

What You'll Be Able To Do

Identify continuous sequences and boundary gaps within interval-based datasets.
Retrieve prior interval boundaries using the LAG window function ordered by interval start and end times.
Flag interval transition boundaries using conditional CASE logic based on boundary overlaps.
Generate persistent island group identifiers using a cumulative SUM window function.
Aggregate grouped interval partitions into consolidated start and end ranges using MIN and MAX.

Detailed Concept Walkthrough

1. Interval Gaps and Islands Concept

An island represents an unbroken, continuous block of sequential or overlapping records, whereas a gap is a break or missing interval between those blocks. When dealing with date intervals, islands can contain rows that touch, overlap on one boundary, or are fully enclosed within an earlier record.

Mechanism: Unlike discrete sequential ID gaps, date interval islands require evaluating whether a current interval's start boundary begins before or at the maximum previous end boundary. Any interval starting strictly after the preceding interval's end boundary marks the end of the previous island and the birth of a gap.
Execution Flow: The dataset is ordered chronologically by start date and end date to ensure that each record is evaluated against the immediately preceding timeline context.
Best Practice: Always inspect whether boundary dates are inclusive or exclusive; identical start and end points between adjacent rows determine whether they merge into one island or start a new island.

-- Inspecting raw interval records that contain overlaps and enclosures
SELECT 
    row_id, 
    start_date, 
    end_date
FROM interval_data
ORDER BY start_date, end_date;

Key Takeaway: Overlapping or enclosed ranges are part of the same island as long as no chronological gap exists between them.

2. Boundary Comparison with LAG

The LAG window function pulls the end boundary of the preceding row into the current row's evaluation context to detect boundary disconnects.

Mechanism: By specifying LAG(end_date) OVER (ORDER BY start_date, end_date), the query exposes the prior interval's closing timestamp to the current row without requiring a self-join. If the current row's start date exceeds this lagged end date (or if the lagged value is NULL), a new island has started.
Under the Hood: SQL Server evaluates the window frame across the ordered partition in memory, scanning rows sequentially to fetch previous row attributes at minimal computational cost.
Syntax Rule: The ORDER BY clause inside LAG must match the physical sequence of your intervals, sorting primarily on start_date and secondarily on end_date to maintain deterministic ordering.

-- Stage 1: Assign sequencing and fetch the prior row's end boundary
SELECT
    start_date,
    end_date,
    ROW_NUMBER() OVER (ORDER BY start_date, end_date) AS row_num,
    LAG(end_date) OVER (ORDER BY start_date, end_date) AS prev_end_date
FROM interval_data;

Key Takeaway: LAG brings the previous row's boundary into the current row, enabling direct row-to-row gap detection.

3. Island Identification via Cumulative Sum

A binary indicator marks the start of each island, and a cumulative running sum of these indicators creates a unique, grouping-ready identifier for every row.

Mechanism: A CASE statement evaluates whether start_date is greater than prev_end_date; it returns 1 for a new island (or the table's first row) and 0 for continuous/overlapping rows. A running SUM of this binary indicator increments by 1 only when a gap occurs, remaining constant across all records belonging to the same island.
Execution Flow: The cumulative SUM function processes rows in chronological sequence, effectively partitioning the records into discrete numeric buckets.
Nuance: Fully enclosed intervals (where both start and end fall inside a previous range) maintain an indicator of 0 because their start date is prior to the active range's boundary.

-- Stage 2: Create binary start indicator and compute running island ID
WITH lagged_data AS (
    SELECT
        start_date,
        end_date,
        ROW_NUMBER() OVER (ORDER BY start_date, end_date) AS row_num,
        LAG(end_date) OVER (ORDER BY start_date, end_date) AS prev_end_date
    FROM interval_data
)
SELECT
    start_date,
    end_date,
    CASE 
        WHEN prev_end_date IS NULL OR start_date > prev_end_date THEN 1 
        ELSE 0 
    END AS is_island_start,
    SUM(CASE WHEN prev_end_date IS NULL OR start_date > prev_end_date THEN 1 ELSE 0 END)
        OVER (ORDER BY row_num) AS island_id
FROM lagged_data;

Key Takeaway: Running sums over conditional binary indicators transform boundary break events into groupable island IDs.

4. Interval Aggregation and Consolidation

Grouping by the computed island ID and aggregating the start and end columns merges fragmented intervals into single consolidated rows.

Mechanism: Using GROUP BY island_id with MIN(start_date) and MAX(end_date) collapses all overlapping, enclosed, and adjacent records within each partition into one summary record covering the entire span.
Under the Hood: The database engine hashes or streams rows by island_id, reducing multiple detailed rows down to exact island boundaries in a single aggregation step.
Best Practice: Encapsulate intermediate logic in Common Table Expressions (CTEs) or derived tables to keep the final SELECT statement clean and readable for reporting layers.

-- Stage 3: Consolidate intervals into final island ranges
WITH lagged_data AS (
    SELECT
        start_date,
        end_date,
        ROW_NUMBER() OVER (ORDER BY start_date, end_date) AS row_num,
        LAG(end_date) OVER (ORDER BY start_date, end_date) AS prev_end_date
    FROM interval_data
),
island_groups AS (
    SELECT
        start_date,
        end_date,
        SUM(CASE WHEN prev_end_date IS NULL OR start_date > prev_end_date THEN 1 ELSE 0 END)
            OVER (ORDER BY row_num) AS island_id
    FROM lagged_data
)
SELECT
    island_id,
    MIN(start_date) AS island_start_date,
    MAX(end_date) AS island_end_date
FROM island_groups
GROUP BY island_id
ORDER BY island_start_date;

Key Takeaway: Grouping on island_id with MIN and MAX collapses complex overlapping fragments into unified timeline spans.

Topics Covered in Consecutive Days, Sessions — Gaps and Islands

Introduction to Gaps and Islands (0:00 - 0:45) — The playing card analogy illustrates sequential island groups and missing gap elements.
Date Range Overlap Complexities (0:45 - 1:30) — Sample data highlights challenges with overlapping and fully enclosed interval records.
Lag Function and Row Numbering (1:30 - 2:15) — Row numbering and the LAG function capture the previous interval boundary.
Island Indicator and Running Total (2:15 - 2:55) — A conditional start indicator combined with a running SUM assigns group IDs.
Aggregation into Island Ranges (2:55 - 3:40) — Grouping by island ID with MIN and MAX consolidates fragmented date ranges.

SQL Cheat Sheet

LAG(column) OVER (ORDER BY ...) — Fetches a value from the preceding row within an ordered set
```
LAG(end_date) OVER (ORDER BY start_date, end_date)
```
ROW_NUMBER() OVER (ORDER BY ...) — Generates a sequential integer for each row based on ordering
```
ROW_NUMBER() OVER (ORDER BY start_date, end_date)
```
CASE WHEN start > prev_end THEN 1 ELSE 0 END — Flags interval break points as binary start indicators
```
CASE WHEN start_date > prev_end_date THEN 1 ELSE 0 END
```
SUM(flag) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) — Computes cumulative running total to assign incremental island IDs
```
SUM(is_island_start) OVER (ORDER BY row_num)
```
MIN(start_date) ... MAX(end_date) GROUP BY island_id — Collapses grouped island records into unified start and end boundaries
```
SELECT island_id, MIN(start_date), MAX(end_date) GROUP BY island_id
```

Comparison Table

Relationship Type	Boundary Condition	Island Behavior
Continuous / Touching	Current start equals previous end	Merged into existing island
Overlapping Boundary	Current start before previous end	Merged into existing island
Fully Enclosed	Both boundaries inside previous range	Merged into existing island
Disconnected Gap	Current start after previous end	Triggers new island start

Common Pitfalls

Mistake: Ordering window functions only by start date when end dates vary. Avoid: Sort deterministically on both start_date and end_date.
Mistake: Using self-joins instead of window functions for gap detection. Avoid: Use LAG and cumulative SUM to prevent exponential join explosion.
Mistake: Forgetting to handle NULL on the first row lagged value. Avoid: Treat NULL lagged boundaries as an automatic island start indicator.

FAQs

Why is this problem harder than traditional discrete gaps and islands? Discrete problems evaluate single integer steps, whereas date interval problems must handle overlapping boundaries and fully enclosed time spans.
Is the binary island start indicator strictly required in production queries? No, you can embed the CASE condition directly inside the cumulative SUM window function without creating an intermediate column.
What happens if an interval is completely contained within an earlier interval? Its start date will be earlier than the previous end date, correctly assigning it an indicator of 0 to keep it in the same island.

Common pitfalls

Mistake: Ordering window functions only by start date when end dates vary. Avoid: Sort deterministically on both start_date and end_date.
Mistake: Using self-joins instead of window functions for gap detection. Avoid: Use LAG and cumulative SUM to prevent exponential join explosion.
Mistake: Forgetting to handle NULL on the first row lagged value. Avoid: Treat NULL lagged boundaries as an automatic island start indicator.

What's up, everyone, and thanks for joining me again. This week we're going to be talking about the gaps and islands problem with a slight variation from the way it's normally covered. You may have noticed that the scenery is a little different. I decided it's finally time to set up a more permanent location for my filming, so I've relegated my camera and equipment down to the basement. Right now, it's kind of a work in progress. I'm hoping to improve it gradually over time, so if things change from week to week, you'll know why. So, the traditional gaps and islands problem looks a little bit like this. Imagine you have some playing cards down on a table and they are laid out in sequential order. You'll see we have ace, two, three, but then we have a missing four and five before we get to the six of hearts. And then we have another missing seven of hearts before we get to eight, nine, and 10. If we group the cards together that don't have any missing cards from their sequences, those are our islands, and the missing cards in between them are known as our gaps. And there's multiple different ways that you can solve a gaps and islands-type problem in SQL Server. The technique I'm going to show you today is using window functions, and the problem itself is a little bit more complicated than our card example. So let's take a look at some test data first. So, here's the table of test data that we're going to be working with today. You'll notice that there's a start date and an end date column, so each row contains a range of dates. I've color-coded the three islands that we're going to be searching for in this data above, and the key thing I want you to notice is that while the end date of some rows match the start date of other rows, for example, rows six and seven, the date ranges of other rows are either fully contained with other rows, like row two is contained within row one, while other rows overlap only on one boundary, so row four's end date doesn't overlap with any other rows, but its start date is before row three's end date. These kind of overlapping or fully enclosed date ranges make the gaps and islands problem a little bit more interesting because we're not only looking for the sequential order of our data, but also where our data is overlapping, and sometimes our data is completely within another range. So, the first thing we need to do is create a row number column, which is going to create a sequence for our data based on the start and end date orders. We're also going to add a previous end date column, which just uses the LAG function to grab the previous row's end date and bring it up to our current row. In order to be able to use these two new fields easily, we're going to put that whole query into a derived table, and then in our new SELECT statement, we're going to create two more columns. The first one is the island start indicator, which is simply a one or zero indicator that identifies when a row is the beginning of a new island. We don't really need this column in order to have our final solution work, but I think it's beneficial to see the output of it because then the next column that we're about to add makes a little bit more sense if we could see the data. And the next column we're going to add is the island ID, which indicates which island group the current row belongs to. So earlier, in our example of date ranges, we identified that there's going to be three islands in our data, so we expect that island ID to increase from one to two to three. And this island ID field is just a sum of the island start indicator column, which is the same way you would do a running total using window functions, which I've covered in a video in the past, so if you're interested in checking that out, click the link below. And so for the final step of our query, we just need to once again take our query, put it into a derived table, and then in our SELECT, choose the min and max start and end dates, and then group that data on our island ID. And so the results that this query now returns are three rows indicating the date ranges of our three islands. This is a really cool query because it's able to take all those overlapping date ranges or those fully enclosed date ranges, figure out, you know, where they're the date ranges are continuous and when they're not, and then create these three islands of data for you. This is a really handy way of taking a lot of detailed data and being able to aggregate it so it's easier to look at if you want to use it in something like a report or other logic. And that concludes today's episode. If you enjoyed what you learned, be sure to press the subscribe button below. By subscribing, you'll be notified of new videos that way you don't miss a video so that you can continue learning and improving your SQL knowledge every single week. So thanks again for tuning in. I'll see you next time.

Frequently asked questions

Why is this problem harder than traditional discrete gaps and islands?

Discrete problems evaluate single integer steps, whereas date interval problems must handle overlapping boundaries and fully enclosed time spans.

Is the binary island start indicator strictly required in production queries?

No, you can embed the CASE condition directly inside the cumulative SUM window function without creating an intermediate column.

What happens if an interval is completely contained within an earlier interval?

Its start date will be earlier than the previous end date, correctly assigning it an indicator of 0 to keep it in the same island.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.