A Daily Streak in SQL: Finding the Longest Run of Consecutive Days

Ten activity rows do not prove ten days of steady activity. Finding consecutive days starts by reducing timestamps to one row per person per day. A gaps-and-islands query then groups uninterrupted runs, so you can report both the longest streak and the streak still active now.

Define the Day Before Defining the Streak

Activity timestamps need a business time zone before they become dates. UTC storage is useful, but a business day in a local reporting zone does not begin at UTC midnight. Convert consistently before casting to date. Applying the wrong boundary changes streaks around midnight without changing any activity records.

I ask whether today's activity is required for a current streak. Some reports allow the current run to end yesterday because today is still in progress. Others require a check-in today. Both rules are reasonable when written down. A query cannot settle that product decision by accident.

The examples use already-defined business dates so the streak logic remains clear. The values are synthetic inputs. If your source contains datetimeoffset values, perform the approved time-zone conversion first and retain the original timestamp for audit. Daylight-saving transitions also make calendar dates a better unit than assuming every business day is exactly twenty-four elapsed hours.

Define consecutive days using the business date before subtracting row numbers or calculating a longest run.

Deduplicate Activity to One Date per User

Several logins on one day count as one active day. Start with DISTINCT over the person identifier and the cast business date. Without that step, extra activity rows create extra row numbers and break the grouping trick. High activity within a day should not shorten a streak.

The temporary input includes a duplicate day and a gap. That gives you a controlled test for both behaviors. The user identifier separates independent streaks. Do not partition by a display name that can change or collide. Use the stable key used by the application.

I keep the daily set as an explicit step because it is easy to inspect. Before running the islands query, check that there is one row per user and date. If the daily set is wrong, every later aggregate will be confidently wrong too. SQL is very willing to organize a mistaken definition neatly.

CREATE TABLE #DailyActivity(UserID int,ActivityTime datetime2);
INSERT #DailyActivity VALUES
(1,'2026-09-20T08:00:00'),(1,'2026-09-20T10:00:00'),(1,'2026-09-21T08:00:00'),
(1,'2026-09-23T08:00:00'),(1,'2026-09-24T08:00:00'),(2,'2026-09-24T09:00:00');
SELECT DISTINCT UserID,CAST(ActivityTime AS date) AS ActivityDate
INTO #Days
FROM #DailyActivity;
SELECT * FROM #Days ORDER BY UserID,ActivityDate;

Group Consecutive Days With a Row Number Key

Within each user, ROW_NUMBER increases by one for each distinct ordered day. During an uninterrupted run, the date also increases by one. Subtracting that row number from the date therefore produces the same group key across the run. A missing day changes the key and starts another island.

DATEADD uses a negative integer offset in this example. Convert the row number explicitly for the traditional DATEADD integer argument. A practical date domain also bounds the number of distinct days a person can have. Keep invalid sentinel dates out of the calculation if subtracting the offset would exceed the date range.

The group key is a technical grouping device, not a meaningful date to display. Show the streak's minimum date, maximum date, and length instead. The next query materializes those aggregates so later examples can reuse them in the same session. Inspect the islands before selecting winners.

WITH Numbered AS
(
 SELECT UserID,ActivityDate,
 DATEADD(day,-CONVERT(int,ROW_NUMBER() OVER
 (PARTITION BY UserID ORDER BY ActivityDate)),ActivityDate) AS IslandKey
 FROM #Days
)
SELECT UserID,MIN(ActivityDate) AS StartDate,MAX(ActivityDate) AS EndDate,
       COUNT_BIG(*) AS StreakDays
INTO #Streaks
FROM Numbered
GROUP BY UserID,IslandKey;
SELECT * FROM #Streaks ORDER BY UserID,StartDate;

Pick the Longest Run of Consecutive Days With a Tie Rule

A user can have several equally long streaks. Decide whether the report returns all tied runs or chooses one. ROW_NUMBER chooses a single winner when its ordering includes a deterministic tie breaker. RANK can preserve ties when that is the requirement.

The query below chooses the most recently ending run among equal lengths. That rule belongs in the report description. Otherwise, two implementations can return different winners while each calculates the same maximum length. Keep the start and end dates with the length so readers can understand the selected run. In the sample, user 1 has two runs of two days, and this rule returns the run ending on 24 September.

Which result would your reader expect when two streaks have equal length? Ask that before naming a field LongestStreak. The name alone does not explain the tie rule. Also include users with no activity through a separate user-table join if they belong in the report. The activity-derived set cannot invent those missing users.

WITH Ranked AS
(
 SELECT *,ROW_NUMBER() OVER
 (PARTITION BY UserID ORDER BY StreakDays DESC,EndDate DESC,StartDate DESC) AS ChoiceNumber
 FROM #Streaks
)
SELECT UserID,StartDate,EndDate,StreakDays
FROM Ranked WHERE ChoiceNumber=1;

Calculate the Current Run From a Fixed As-Of Date

A current streak needs an as-of date in the same business time zone as the activity dates. Do not compare local dates with a UTC date taken directly from the server clock. Fix the as-of value for repeatable tests and historical reports.

The next query requires activity on the as-of date. To allow a run ending yesterday, adjust the endpoint predicate to the approved rule and select the latest eligible ending run per user. Exclude future dates before calculating the report if delayed or incorrect source clocks can create them.

Retain the as-of date in the output or report metadata. A current streak changes when the date changes even if no new rows arrive. That is expected behavior, but it needs to be reproducible when someone asks why a displayed streak disappeared. The query should explain the reporting rule instead of relying on the moment somebody happened to press Execute.

DECLARE @AsOf date='20260924';
SELECT UserID,StartDate,EndDate,StreakDays,@AsOf AS ReportDate
FROM #Streaks
WHERE EndDate=@AsOf;

Test the Boundaries and Support the Daily Set

Test duplicate activity, a one-day run, a gap, equal longest runs, and an unfinished current day. Include timestamps near the business midnight boundary in the source conversion tests. These cases expose definition errors that a long uninterrupted demonstration would hide.

On a large table, consider an indexed business-date representation or a maintained daily activity table when the workload repeatedly asks the same question. Keep the time-zone rule stable and documented. A stored date derived under the wrong rule makes the query fast without making it correct.

Review the plan for the distinct daily set and ordered window calculation. Sorting and duplicate reduction have real costs. Filter the relevant observation period carefully, remembering that cutting off an earlier run also cuts its reported length. Consecutive days describe a calendar relationship, so preserve the boundaries that give that relationship meaning.

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 an activity count, it is an unbroken run under a clear day rule.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.