How do you find the longest streak of consecutive days in a table of dates? The trick is called gaps and islands, and it needs only one ROW_NUMBER, one DATEADD and a GROUP BY.
I have written a blog post every single day for many years, so streaks are close to my heart. The same question shows up in real systems all the time: how many days in a row did a customer log in, how long did a machine run without an error, how many consecutive days did a store make a sale? In each case you have one row per event, and you want to know where the unbroken runs start and end.
The Sample Data for the Longest Streak
Here is a small log of blog posts. Notice two details on purpose: January 2 has two posts, and some days have none.
CREATE TABLE dbo.PostLog (PostedAt datetime2(0) NOT NULL);
INSERT INTO dbo.PostLog (PostedAt) VALUES
('2026-01-01T07:00:00'),('2026-01-02T07:00:00'),('2026-01-02T18:30:00'),
('2026-01-03T07:00:00'),('2026-01-04T07:00:00'),
('2026-01-06T07:00:00'),('2026-01-07T07:00:00'),('2026-01-08T07:00:00'),
('2026-01-09T07:00:00'),('2026-01-10T07:00:00'),
('2026-01-13T07:00:00'),('2026-01-14T07:00:00');Looking at it by eye, there are three streaks: January 1 to 4, January 6 to 10, and January 13 to 14. Now let us make SQL Server find them.
Finding the Longest Streak With Gaps and Islands
Each streak is an island of consecutive dates, and the missing days between them are the gaps. The idea is simple once you see it. Number the dates in order with ROW_NUMBER, then subtract that number of days from each date. Inside one streak, the date and the row number both go up by one each day, so the result stays the same. The moment a day is skipped, the date jumps ahead of the row number and the result changes. That constant value becomes a key for the whole island.
WITH Days AS (
SELECT DISTINCT CAST(PostedAt AS date) AS DayPosted
FROM dbo.PostLog
),
Grouped AS (
SELECT DayPosted,
DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY DayPosted), DayPosted) AS IslandKey
FROM Days
)
SELECT MIN(DayPosted) AS StreakStart,
MAX(DayPosted) AS StreakEnd,
COUNT(*) AS StreakDays
FROM Grouped
GROUP BY IslandKey
ORDER BY StreakDays DESC, StreakStart;On my SQL Server 2025 test instance it returns the three streaks we saw by eye, longest first: January 6 to 10 with 5 days, January 1 to 4 with 4 days, and January 13 to 14 with 2 days. To get only the longest streak, add TOP (1) to the final SELECT.
Why DISTINCT Matters: The Duplicate Day Trap
The first common table expression turns each timestamp into a date and removes duplicates. That step is not decoration. If you skip it and number the raw rows, the second post on January 2 gets its own row number, and the math drifts by one from that point on. When I ran the query without DISTINCT, it reported a false 7 day streak from January 1 to 10 and a second overlapping streak from January 2 to 4. The real longest streak is 5 days. Always reduce to one row per day before you number anything.
The Current Streak Ending Today
Dashboards often ask a different question: how long is the streak right now? Find the island that contains today and count its days.
DECLARE @Today date = '2026-01-14';
WITH Days AS (
SELECT DISTINCT CAST(PostedAt AS date) AS DayPosted
FROM dbo.PostLog
),
Grouped AS (
SELECT DayPosted,
DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY DayPosted), DayPosted) AS IslandKey
FROM Days
)
SELECT COUNT(*) AS CurrentStreakDays
FROM Grouped
WHERE IslandKey = (SELECT IslandKey FROM Grouped WHERE DayPosted = @Today);With the sample data it returns 2, for January 13 and 14. If there is no row for today, the inner query finds nothing and the result is 0, which is exactly what a broken streak should show. In a real report, set @Today with CAST(SYSDATETIME() AS date), or better, with the business time zone as explained below.
Listing the Gaps Between Streaks
Sometimes the gaps are the interesting part: which days did we miss, and for how long? LAG gives each date the previous date, so any jump of more than one day is a gap.
WITH Days AS (
SELECT DISTINCT CAST(PostedAt AS date) AS DayPosted
FROM dbo.PostLog
),
Paired AS (
SELECT DayPosted,
LAG(DayPosted) OVER (ORDER BY DayPosted) AS PreviousDay
FROM Days
)
SELECT DATEADD(DAY, 1, PreviousDay) AS GapStart,
DATEADD(DAY, -1, DayPosted) AS GapEnd,
DATEDIFF(DAY, PreviousDay, DayPosted) - 1 AS MissedDays
FROM Paired
WHERE DATEDIFF(DAY, PreviousDay, DayPosted) > 1
ORDER BY GapStart;It returns two gaps: January 5 with 1 missed day, and January 11 to 12 with 2 missed days. Together with the islands query, you now have the full picture of the streaks and the breaks between them.
Things to Get Right Before You Trust the Longest Streak
- Decide what a day means. CAST to date uses whatever time the column stores. If the column holds UTC and your users live in another time zone, a post written late in the evening can land on the next day and split a real streak in two. Convert to the business time zone first, for example with AT TIME ZONE, and then cast to date.
- Group per person when needed. For a streak per customer or per machine, add PARTITION BY CustomerID to ROW_NUMBER and CustomerID to the GROUP BY.
- Index the date column. On a large log, an index on the date (and the partition column, if you use one) lets SQL Server read the rows in order instead of sorting them first.
- Other intervals work too. The same trick finds consecutive weeks or months: number the rows and subtract that many weeks or months with DATEADD.
After many years of writing every day, I can tell you that a streak is only as honest as the query that counts it. Remove the duplicates, agree on what a day means, and the numbers will tell the truth.
Related reading on this blog: Gaps and Islands: Finding Missing Ranges in a Sequence and The Four Window Functions You Will Actually Use.
A streak is not a count of rows, it is a run of days with no gap between them.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
