Gaps and Islands problems turn messy sequences into meaningful runs and breaks.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
An island is a consecutive run. A gap is the break between runs. The SQL challenge is to convert row-by-row continuity into a group identifier that can be aggregated.
For daily attendance, consecutive may mean adjacent calendar dates. For machine telemetry, it may mean status values with no change. For invoices, it may mean sequential document numbers.
Values:
1, 2, 3, 6, 7, 8, 12
Islands:
1-3
6-8
12
Gaps:
4-5
9-11Gaps and islands is not one SQL trick; it is a family of patterns built around detecting where continuity breaks.
LAG is one of the clearest ways to detect continuity breaks. It lets each row see the previous value so you can ask whether the current row continues the run or starts a new one.
If yesterday is the previous date, today's row continues the island. If the previous date was three days ago, today's row begins a new island.
WITH x AS
(
SELECT EmployeeID,
WorkDate,
LAG(WorkDate) OVER
(
PARTITION BY EmployeeID
ORDER BY WorkDate
) AS PrevDate
FROM dbo.Attendance
)
SELECT *,
CASE
WHEN PrevDate IS NULL THEN 1
WHEN DATEDIFF(day, PrevDate, WorkDate) > 1 THEN 1
ELSE 0
END AS NewIslandFlag
FROM x;LAG converts continuity from an abstract idea into a row-by-row comparison you can test.
Once each new island is marked with 1, a running SUM can assign a stable group number. Every time a break appears, the group number increments.
Rows 1, 2 and 3 may all have GroupID = 1. After a gap, rows 6, 7 and 8 receive GroupID = 2.
WITH Breaks AS
(
SELECT EmployeeID,
WorkDate,
CASE
WHEN LAG(WorkDate) OVER
(PARTITION BY EmployeeID ORDER BY WorkDate) IS NULL
OR DATEDIFF
(
day,
LAG(WorkDate) OVER
(PARTITION BY EmployeeID ORDER BY WorkDate),
WorkDate
) > 1
THEN 1 ELSE 0
END AS NewIslandFlag
FROM dbo.Attendance
),
Grouped AS
(
SELECT *,
SUM(NewIslandFlag) OVER
(
PARTITION BY EmployeeID
ORDER BY WorkDate
ROWS UNBOUNDED PRECEDING
) AS IslandID
FROM Breaks
)
SELECT *
FROM Grouped;The running-SUM pattern is one of the most reusable gaps-and-islands techniques in SQL Server.
Once rows share an IslandID, the problem becomes ordinary aggregation. You can calculate start, end, duration, count and other business measures for each run.
A machine may have an 'Online' island from 08:00 to 11:42 and another from 12:03 to 18:10. Those periods can now be treated as business objects.
SELECT EmployeeID,
IslandID,
MIN(WorkDate) AS StartDate,
MAX(WorkDate) AS EndDate,
COUNT(*) AS DaysInRun
FROM Grouped
GROUP BY EmployeeID, IslandID
ORDER BY EmployeeID, StartDate;After grouping, an island becomes a compact interval the business can report, rank and compare.
After islands are summarized, LEAD can expose the next island's start. That makes it easy to compute the missing interval between current EndDate and next StartDate.
If an attendance streak ends on May 10 and the next begins May 14, the gap is May 11 through May 13.
WITH IslandSummary AS
(
SELECT EmployeeID,
IslandID,
MIN(WorkDate) AS StartDate,
MAX(WorkDate) AS EndDate
FROM Grouped
GROUP BY EmployeeID, IslandID
),
x AS
(
SELECT *,
LEAD(StartDate) OVER
(
PARTITION BY EmployeeID
ORDER BY StartDate
) AS NextStartDate
FROM IslandSummary
)
SELECT EmployeeID,
DATEADD(day, 1, EndDate) AS GapStart,
DATEADD(day, -1, NextStartDate) AS GapEnd
FROM x
WHERE NextStartDate > DATEADD(day, 1, EndDate);Gaps are easiest to calculate after islands have already been reduced to start/end intervals.
Not every island is defined by numeric adjacency. In telemetry, workflow and operations data, an island can mean a continuous run of the same status until the value changes.
A machine may be Online for six events, Offline for three, then Online again. The two Online runs are separate islands even though the status text is the same.
WITH x AS
(
SELECT DeviceID,
EventTime,
Status,
LAG(Status) OVER
(
PARTITION BY DeviceID
ORDER BY EventTime
) AS PrevStatus
FROM dbo.DeviceStatus
),
Breaks AS
(
SELECT *,
CASE
WHEN PrevStatus IS NULL OR PrevStatus <> Status
THEN 1 ELSE 0
END AS NewRunFlag
FROM x
)
SELECT *,
SUM(NewRunFlag) OVER
(
PARTITION BY DeviceID
ORDER BY EventTime
ROWS UNBOUNDED PRECEDING
) AS RunID
FROM Breaks;The same break-flag and running-SUM idea works for state changes as well as missing dates.
For fixed-step sequences, an elegant gaps-and-islands trick is to subtract ROW_NUMBER from the value. Within a consecutive run, both the value and row number increase together, so their difference stays constant.
For 10,11,12 and row numbers 1,2,3, the difference is always 9. After a gap, the difference changes and a new island appears.
WITH x AS
(
SELECT EmployeeID,
WorkDate,
ROW_NUMBER() OVER
(
PARTITION BY EmployeeID
ORDER BY WorkDate
) AS rn
FROM dbo.Attendance
),
g AS
(
SELECT *,
DATEADD(day, -rn, WorkDate) AS IslandKey
FROM x
)
SELECT EmployeeID,
MIN(WorkDate) AS StartDate,
MAX(WorkDate) AS EndDate,
COUNT(*) AS DaysInRun
FROM g
GROUP BY EmployeeID, IslandKey;Use the offset trick when the sequence step is fixed; use break flags when the business rule is more flexible.
There is no single universal gaps-and-islands query. LAG + running SUM is flexible. ROW_NUMBER offset is elegant for fixed-step sequences. Calendar tables help when you need explicit missing dates. The right solution starts with the business definition of continuity.
If 'consecutive' means adjacent business days, a calendar table may be required. If it means identical statuses until change, a status comparison is enough.
1. What entity owns the sequence?
2. What column defines order?
3. What exactly counts as consecutive?
4. What starts a new island?
5. Are duplicate timestamps/values possible?
6. Do you need islands, gaps, or both?
7. Is LAG + running SUM clearer?
8. Is ROW_NUMBER offset valid for a fixed step?
9. Are calendar/business-day rules involved?
10. Are indexes aligned with entity + sequence order?
11. Have edge cases been tested?
12. Are logical reads / sorts acceptable at scale?Gaps and islands is a modeling problem first and a window-function problem second.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
A consecutive run of rows connected by the continuity rule.
It exposes the previous ordered value so the current row can be tested for continuity or a break.
A running SUM turns break flags into island group numbers.
For fixed-step sequences where the derived offset remains constant within an island.
Define exactly what consecutive means before choosing the SQL pattern.
| Problem shape | Strong pattern |
|---|---|
| Irregular break rule | LAG + Break Flag + Running SUM |
| Fixed-step numbers/dates | ROW_NUMBER Offset |
| Same status until change | LAG(Status) + Running SUM |
| Need exact missing dates | Calendar / tally table comparison |
| Need summarized runs | GROUP BY IslandID + MIN/MAX/COUNT |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
Gaps and islands is a sequence-modeling problem powered by window functions.