Write a SQL query that returns each user's longest streak of consecutive UTC login dates.

• Deduplicate to one row per user and UTC date first.
• Consecutive means dates differ by exactly one day.
• Choose the most recent streak when lengths tie.
• Return one row per user with at least one login.

After deduplicating dates, I number each user's days in chronological order. Subtracting that row number from each date creates a constant group key across consecutive dates. I aggregate those islands into streaks, then rank the streaks by length and recency to select one result per user.

WITH LoginDays AS
(
    SELECT DISTINCT
        l.UserId,
        CONVERT(date, l.LoggedInAtUtc) AS LoginDate
    FROM dbo.UserLogins AS l
),
NumberedDays AS
(
    SELECT
        UserId,
        LoginDate,
        ROW_NUMBER() OVER
        (
            PARTITION BY UserId
            ORDER BY LoginDate
        ) AS DayNumber
    FROM LoginDays
),
Islands AS
(
    SELECT
        UserId,
        LoginDate,
        DATEADD(day, -CONVERT(int, DayNumber), LoginDate) AS IslandKey
    FROM NumberedDays
),
Streaks AS
(
    SELECT
        UserId,
        MIN(LoginDate) AS StreakStart,
        MAX(LoginDate) AS StreakEnd,
        COUNT_BIG(*) AS StreakDays
    FROM Islands
    GROUP BY UserId, IslandKey
),
RankedStreaks AS
(
    SELECT
        UserId,
        StreakStart,
        StreakEnd,
        StreakDays,
        ROW_NUMBER() OVER
        (
            PARTITION BY UserId
            ORDER BY StreakDays DESC, StreakEnd DESC
        ) AS rn
    FROM Streaks
)
SELECT UserId, StreakStart, StreakEnd, StreakDays
FROM RankedStreaks
WHERE rn = 1
ORDER BY UserId;

The query sorts distinct dates per user and then groups the derived islands. An index on (UserId, LoggedInAtUtc) helps the initial read, but converting a huge event history to dates is still substantial work. A maintained daily activity table is often the better source for recurring streak features.

• Several logins on one date.
• Two longest streaks with equal length.
• A leap day or month boundary.
• A user with one login only.

• Add duplicate same-day logins to catch inflated streak lengths.
• Build a streak across month and year boundaries.
• Create tied streaks and verify the latest wins.
• Use users with independent date sequences.

• Numbering raw login events instead of distinct dates.
• Using date differences from the previous row without completing island grouping.
• Leaving the tie rule undefined.
• Calculating an expensive product feature repeatedly from raw event history.