Find the longest consecutive streak of daily logins for each user.

Longest Consecutive Daily Login Streak per User (SQL)

Table

CREATE TABLE UserLogins (
    UserId    INT,
    LoginTime DATETIME2
);

Sample data:

INSERT INTO UserLogins (UserId, LoginTime) VALUES
(1, '2026-10-01 08:15'), (1, '2026-10-01 19:40'),
(1, '2026-10-02 09:00'), (1, '2026-10-03 10:30'),
(1, '2026-10-06 07:45'), (1, '2026-10-07 08:05'),
(2, '2026-10-01 12:00'), (2, '2026-10-03 12:00'), (2, '2026-10-04 12:00'),
(3, '2026-10-05 22:10');

Approach: gaps and islands

1. Reduce logins to one row per user per day (users can log in many times a day).
2. Number each user's days with ROW_NUMBER().
3. Subtract that number from the date. For consecutive days the result is constant, so it identifies the streak ("island").
4. Group by that value, count days per streak, and take the maximum per user.

Query (SQL Server / Azure SQL)

WITH DistinctDays AS (
    SELECT DISTINCT
        UserId,
        CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Numbered AS (
    SELECT
        UserId,
        LoginDate,
        DATEADD(DAY,
                -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate),
                LoginDate) AS StreakKey
    FROM DistinctDays
),
Streaks AS (
    SELECT
        UserId,
        MIN(LoginDate) AS StreakStart,
        MAX(LoginDate) AS StreakEnd,
        COUNT(*)       AS StreakDays
    FROM Numbered
    GROUP BY UserId, StreakKey
),
Ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY UserId
               ORDER BY StreakDays DESC, StreakStart DESC
           ) AS rn
    FROM Streaks
)
SELECT UserId, StreakStart, StreakEnd, StreakDays AS LongestStreak
FROM Ranked
WHERE rn = 1
ORDER BY UserId;

Result

UserId	StreakStart	StreakEnd	LongestStreak
1	2026-10-01	2026-10-03	3
2	2026-10-03	2026-10-04	2
3	2026-10-05	2026-10-05	1

How the streak key works

For user 1, after de-duplicating days:

LoginDate	ROW_NUMBER	LoginDate - ROW_NUMBER (StreakKey)
2026-10-01	1	2026-09-30
2026-10-02	2	2026-09-30
2026-10-03	3	2026-09-30
2026-10-06	4	2026-10-02
2026-10-07	5	2026-10-02

Consecutive days share the same key. A gap changes it, starting a new island.

Details

• De-duplicate first. Without DISTINCT on the date, multiple logins in a day break the row numbering and the streak math.
• CAST(... AS DATE) removes the time portion. If your timestamps are stored in UTC and "day" means the user's local day, convert the time zone first (AT TIME ZONE).
• Ties: if a user has two streaks of equal length, the query returns the most recent one (StreakStart DESC). Change the order to ASC for the earliest, or use RANK() to return all tied streaks.
• Users with no logins don't appear. Left join from a Users table if you need them with a 0.
• Only the length, not the dates: replace the last two CTEs with

SELECT UserId, MAX(StreakDays) AS LongestStreak
FROM Streaks
GROUP BY UserId;

Variation: current streak (ending today)

WITH DistinctDays AS (
    SELECT DISTINCT UserId, CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Numbered AS (
    SELECT UserId, LoginDate,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate), LoginDate) AS StreakKey
    FROM DistinctDays
),
Streaks AS (
    SELECT UserId, MIN(LoginDate) AS StreakStart, MAX(LoginDate) AS StreakEnd, COUNT(*) AS StreakDays
    FROM Numbered
    GROUP BY UserId, StreakKey
)
SELECT UserId, StreakStart, StreakEnd, StreakDays AS CurrentStreak
FROM Streaks
WHERE StreakEnd >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE));  -- logged in today or yesterday

Alternative: LAG-based

WITH DistinctDays AS (
    SELECT DISTINCT UserId, CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Flagged AS (
    SELECT UserId, LoginDate,
           CASE WHEN DATEDIFF(DAY,
                    LAG(LoginDate) OVER (PARTITION BY UserId ORDER BY LoginDate),
                    LoginDate) = 1
                THEN 0 ELSE 1 END AS IsNewStreak
    FROM DistinctDays
),
Grouped AS (
    SELECT UserId, LoginDate,
           SUM(IsNewStreak) OVER (
               PARTITION BY UserId ORDER BY LoginDate
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS StreakId
    FROM Flagged
)
SELECT UserId, StreakId,
       MIN(LoginDate) AS StreakStart, MAX(LoginDate) AS StreakEnd, COUNT(*) AS StreakDays
FROM Grouped
GROUP BY UserId, StreakId;

This flags a new streak whenever the previous login day isn't exactly one day earlier, then a running sum assigns a streak ID. It's easier to read and extends to other rules (for example, allowing a one-day gap).

PostgreSQL version

WITH days AS (
    SELECT DISTINCT user_id, login_time::date AS login_date
    FROM user_logins
),
numbered AS (
    SELECT user_id, login_date,
           login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS streak_key
    FROM days
),
streaks AS (
    SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS streak_days
    FROM numbered
    GROUP BY user_id, streak_key
)
SELECT DISTINCT ON (user_id) user_id, streak_start, streak_end, streak_days
FROM streaks
ORDER BY user_id, streak_days DESC, streak_start DESC;

Supporting index

CREATE INDEX IX_UserLogins_User_Time
ON UserLogins (UserId, LoginTime);

This lets the database read each user's logins already ordered. For very large tables, consider a pre-aggregated daily table (UserId, LoginDate) so the DISTINCT step isn't repeated on every run.

Compatibility

Window functions (ROW_NUMBER, LAG) work in Azure SQL, SQL Server 2012+, PostgreSQL, MySQL 8.0+, Oracle, and SQLite 3.25+. Only the date arithmetic changes by dialect: DATEADD / DATEDIFF in SQL Server, date subtraction in PostgreSQL, DATE_SUB / DATEDIFF in MySQL.