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

Table: UserLogins(LoginId, UserId, LoggedInAtUtc). Multiple logins on one date count as one active day. Return UserId, streak start, streak end, and day count for the longest streak. If streak lengths tie, return the most recent streak.

A user logs in twice Monday, once Tuesday, and once Thursday. Monday-Tuesday is a two-day streak; Thursday is a one-day streak. Return Monday through Tuesday.

• 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.